asked on CURRENT_DATE function to show prior month data
How do I use the CURRENT_DATE function in DB2 to pull data for the entire past month on the first day of a new month?
This job runs flawlessly daily with the piece of code below, but on the 1st of the month it returns no results:
ACCT_OPEN_DT >(CURRENT DATE - day(current date) days )
*What do I need to modify to make sure that on the 1st of each month, it still shows the prior month's entire data?
Thanks
This job runs flawlessly daily with the piece of code below, but on the 1st of the month it returns no results:
ACCT_OPEN_DT >(CURRENT DATE - day(current date) days )
*What do I need to modify to make sure that on the 1st of each month, it still shows the prior month's entire data?
Thanks
DB2
Last Comment
saved4use
--
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Let's see. CURRENT DATE - day(current date) days will always return the last day of the previous month. If you wanted to return the FIRST day of the previouos month, you could do:
CURRENT DATE - day(current date) days + 1 day - 1 month
HTH,
DaveSlash
CURRENT DATE - day(current date) days + 1 day - 1 month
HTH,
DaveSlash
If you wanted to return the first day of the current month, you'd do this:
CURRENT DATE - day(current date) days + 1 day
CURRENT DATE - day(current date) days + 1 day
ASKER
@ sathyaram_s, You are correct, that's exactly what I want.
I've changed my code and will see what happens on 4/1.
Thanks
I've changed my code and will see what happens on 4/1.
Thanks