# Convert @YYYYQ to the end of Quarter with TSQL

``` declare @YYYYQ char(5)='20152'

SELECT Cast( Left(@YYYYQ ,4)+ Case when (Right(@YYYYQ,1)-1)  % 3 =0
Then Right('0'+Cast(Right(@YYYYQ,1)*3 as varchar(2)),2) +'31'
Else Right('0'+Cast(Right(@YYYYQ,1)*3 as varchar(2)),2) +'30' END   as Date)

, Left(@YYYYQ ,4)+ Case when (Right(@YYYYQ,1)-1)  % 3 =0
Then Right('0'+Cast(Right(@YYYYQ,1)*3 as varchar(2)),2) +'31'
Else Right('0'+Cast(Right(@YYYYQ,1)*3 as varchar(2)),2) +'30' END

--Or
Select   EOMOnth( Left(@YYYYQ,4)+  Right('0'+Cast(Cast(Right(@YYYYQ,1) as int) *3 as varchar(2)),2)+'01' )

---Another sample

Create table test (id int, YYYYQ varchar(14)
)

Insert into test values(1,'1Q2014 Archive'),
(2,'2Q2014 Archive'),
(3,'3Q2014 Archive'),
(4,'4Q2014 Archive'),
(5,'1Q2015 Archive')

SELECT

Cast( Substring(YYYYQ ,3,4)+ Right('0'+Cast((Left(YYYYQ,1)-1)*3+1 as varchar(2)),2) +'01' as Date)  FirstdayofTheQuarter,

Cast( Substring(YYYYQ ,3,4)+ Case when (Left(YYYYQ,1)-1)  % 3 =0
Then Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'31'
Else Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'30' END  as Date)   LastdayofTheQuarter,

Dateadd(Day,1,
Substring(YYYYQ ,3,4)+ Case when (Left(YYYYQ,1)-1)  % 3 =0
Then Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'31'
Else Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'30' END) LastdayofNextQuarter

From test

---Or

Select
Substring(YYYYQ ,3,4)+  Right('0'+Cast(((Cast(Left(YYYYQ,1) as int)-1) *3 +1)as varchar(2)),2)+'01' FirstdayofTheQuarter
,
EOMOnth( Substring(YYYYQ ,3,4)+  Right('0'+Cast(Cast(Left(YYYYQ,1) as int) *3 as varchar(2)),2)+'01' ) LastdayofTheQuarter
,
Dateadd(day,1,EOMOnth( Substring(YYYYQ ,3,4)+  Right('0'+Cast(Cast(Left(YYYYQ,1) as int) *3 as varchar(2)),2)+'01' ))
FirstdayofNextQuarter

From test

Drop table test

```
Advertisements