Convert date > xx/01/xxxx of given month to first of following month

Database

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

7 REPLIES

Re: Convert date > xx/01/xxxx of given month to first of following month

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

Re: Convert date > xx/01/xxxx of given month to first of following month

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!

Re: Convert date > xx/01/xxxx of given month to first of following month

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

Re: Convert date > xx/01/xxxx of given month to first of following month

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'

Re: Convert date > xx/01/xxxx of given month to first of following month

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 :)

N/A

Re: Convert date > xx/01/xxxx of given month to first of following month

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

Re: Convert date > xx/01/xxxx of given month to first of following month

Thanks Dieter!