Menu
I need to check whether a given date is in the preceding month for a monthly query. I can get
CURRENT DATE - 1 MONTH
to compare the selected date with, but I can't assume the current date will be the first day of each month exactly. Is there a built in way to get the first day of the month without extracting the year and month and gluing it back together?
515
To find the first day, the date time is first converted to date only using TO_DATE function. Then, the date is changed to the first day of the month by using the DATE_FROM_PARTS function. This works by inputting 01' in the 'day' part of the function.
*Subtract the two date values, divide by (60 seconds * 60 minutes * 24 hours * 365.25 days / 12 months). COMPUTE month1 = (date2 - date1) / (60 * 60 * 24 * 365.24 / 12) . EXE . *You may also use the DATEDIFF function.
The DAYOFWEEK function returns an integer, in the range of 1 to 7, that represents the day of the week, where 1 is Sunday and 7 is Saturday. The DAYOFWEEK function is similar to the DAYOFWEEK_ISO function. The schema is SYSIBM.
First Day of the Current Month:
CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS
First of the Current Year is
CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - (MONTH(CURRENT_DATE)-1) MONTHS
Last Day of the Last Month:
CURRENT_DATE - DAY(CURRENT_DATE) DAYS
First Day of the Last Month:
CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - 1 MONTH
First day of this year:
date('0001-01-01') + year(current date) years - 1 year
First day of this month:
date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 1 month
First day of last month:
date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 2 months
If you don't need to maintain SQL compatibility with Db2 LUW v10.5 and older, you can also use these convenient Db2 LUW v11.1 scalar functions: THIS_WEEK() THIS_MONTH() THIS_QUARTER() and THIS_YEAR().
First day of this month:
THIS_MONTH(CURRENT DATE)
First day of last month:
THIS_MONTH(CURRENT DATE) - 1 MONTH
Last day of last month:
THIS_MONTH(CURRENT DATE) - 1 DAY
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
