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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s