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.
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
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.
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'
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 :)
The calculation might be simplified to:
if it's not the first day of a month calculate the last day and add one day
WHEN EXTRACT (DAY FROM COLUMNNAME) = 1
ELSE LAST_DAY(COLUMNNAME) + 1