Calculate end of quarter date for format 'YYYYQ'

Database
Enthusiast

Calculate end of quarter date for format 'YYYYQ'

Hi,

 

I need help with calculating end of quarter date from "YYYYQ" format.. For example "2017Q1" should be converted to '2017-03-31' date.

 

Thanks in advance,

-Raghu

Tags (2)

Accepted Solutions
Junior Contributor

Re: Calculate end of quarter date for format 'YYYYQ'

As there are only 4 quarters this is probably the easiest way:

Cast(Substring(mycol From 1 FOR 4) ||
     CASE Substring(mycol From 6) 
        WHEN '1' THEN '-03-31'
        WHEN '2' THEN '-06-30'
        WHEN '3' THEN '-09-30'
        WHEN '4' THEN '-12-31'
     END AS DATE)

 

1 ACCEPTED SOLUTION
2 REPLIES
Enthusiast

Re: Calculate end of quarter date for format 'YYYYQ'

Hi Raghu,

 

You can try something like this.

 

SyntaxEditor Code Snippet

SELECT 
 EXTRACT ( YEAR FROM DATE)||'Q1',TD_QUARTER_END(DATE,'COMPATIBLE')

Thanks,

Venkat 

Junior Contributor

Re: Calculate end of quarter date for format 'YYYYQ'

As there are only 4 quarters this is probably the easiest way:

Cast(Substring(mycol From 1 FOR 4) ||
     CASE Substring(mycol From 6) 
        WHEN '1' THEN '-03-31'
        WHEN '2' THEN '-06-30'
        WHEN '3' THEN '-09-30'
        WHEN '4' THEN '-12-31'
     END AS DATE)