All Forums Database
cgermain904 5 posts Joined 08/16
22 Aug 2016
Convert date > xx/01/xxxx of given month to first of following month

Hi,
I'm new to the forum and newly learning SQL. I'm hoping for some help on converting dates upon returning the query. The issue I have is that there is a PROCESS_DT field that I'm using for analysis. In most cases the date is the first of the month, however, there are cases it will be a later date in the month but really represents a cycle that should be recorded in the following month. Example:
Row 1 : 1/1/2016
Row 2 : 1/28/2016 - this date or any other not equal to XX/01/XXXX should represent the following month for historical analysis
Row 3 : 3/1/2016
I'm wondering how to write a CASE where condition PROCESS_DT > XX/01/XXXX for that given month, then return the date for the first of the following month. In the example above, the statement should recognize 1/28/2016 as > 1/1/2016 for that month and then convert to 2/1/2016 (first of following month). I hope I explained this clearly and am grateful for any help. 
Regards,
Chris

22 Aug 2016

Try this
SEL CASE WHEN EXTRACT (DAY FROM CURRENT_DATE) <> 1 THEN (CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)) +1 END
Just replace the CURRENT_DATE with your column name

cgermain904 5 posts Joined 08/16
22 Aug 2016

Thank you for your response, Sachin. This CASE yields the date as the first of that particular month, not the following month which is what I need. I've also noticed that records will store on 1/2/XXXX versus 1/1/XXXX, however I could just modify that expression to be "<> 2". 
 
Example of result in a row when comparing the regular column to the case expression:
PROCESS_DT : 2/27/2015
CASE EXPRESSIONS : 2/1/2015 - I need this to show 3/1/2015 
Also, for those dates that land on XX/01/XXXX, I need those to be returned in the expression field as-is.
Thanks!

cgermain904 5 posts Joined 08/16
22 Aug 2016

Correction to above on 'however, could I just modify that expression to be "> 2"?'

22 Aug 2016

SEL CASE WHEN EXTRACT (DAY FROM COLUMNNAME) > 2 THEN ADD_MONTHS((COLUMNNAME - EXTRACT(DAY FROM COLUMNNAME)) +1,1) ELSE COLUMNNAME END
Use your column name inplace of 'COLUMNNAME'

cgermain904 5 posts Joined 08/16
22 Aug 2016

You, my friend, are awesome! Thanks so much, this worked great!
I read more about the ADD_MONTHS and EXTRACT functions to help understand what is happening. Great learning from my first post. I look forward to many more :)

dnoeth 4628 posts Joined 11/04
22 Aug 2016

The calculation might be simplified to:
if it's not the first day of a month calculate the last day and add one day

CASE
   WHEN EXTRACT (DAY FROM COLUMNNAME) = 1
   THEN COLUMNNAME 
   ELSE LAST_DAY(COLUMNNAME) + 1
END

 

Dieter

cgermain904 5 posts Joined 08/16
22 Aug 2016

Thanks Dieter!

You must sign in to leave a comment.