Geek Answers Handbook

Select the first day of the previous month in (DB2) SQL

I need to check if the given date is the previous month for the monthly request. I can get

CURRENT DATE - 1 MONTH

to compare the selected date with, but I can’t assume that the current date will be exactly the first day of each month. Is there a built-in way to get the first day of the month without extracting the year and month and gluing it together?

+6
date sql database db2
source share
6 answers

The first day of this year:

 date('0001-01-01') + year(current date) years - 1 year

The first day of this month:

 date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 1 month

The first day of the last month:

 date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 2 months
+10
source share

The first day of the current month:

 CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS

First year of the current year

 CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - (MONTH(CURRENT_DATE)-1) MONTHS

Last day of the last month:

 CURRENT_DATE - DAY(CURRENT_DATE) DAYS

The first day of the last month:

 CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - 1 MONTH
+11
source share

The following are snippets from my receipt listing for calculating the SQL SQL month:

Beginning of the current month:

 CURRENT DATE + 1 DAYS - DAY(CURRENT DATE) DAYS

End of current month:

 LAST_DAY(CURRENT DATE)

Beginning of the previous month:

 CURRENT DATE + 1 DAYS - DAY(CURRENT DATE) DAYS - 1 MONTHS

End of previous month:

 LAST_DAY(CURRENT DATE - 1 MONTHS)

Beginning of next month:

 CURRENT DATE + 1 DAYS - DAY(CURRENT DATE) DAYS + 1 MONTHS

End of next month:

 LAST_DAY(CURRENT DATE + 1 MONTHS)
+2
source share

To combine this with additional information. The solution above is perfect if you want the first day of the month, say you want the last day of the month, or in my case I want the last day of the next quarter.

From above i did

 date('0001-01-31') + year(date(prevQdate))years - 1 year + month(prevQdate) months + 2 months)

Which did not give me what I wanted, sometimes the date was 30th and not 31st months with a 31-day ...

Change it to

 date('0001-01-31') + year(date(prevQdate))years - 1 year + **(month(prevQdate) + 2)** months)

Gave me what I wanted. It appears that the first addition of the Months from the previous quarter of the date was a reset of a portion of DAY of my date, and thus the second addition of the months worked with a date of only 30 days in the month.

You just have to be careful when using this date processing method in DB2.

+1
source share

I believe the following will work for you.

 ROUND_TIMESTAMP (somedate,'W')
0
source share

LUW 9.7 (and I think z / os) methods using built-in functions.

Last day of the current month:

 LAST_DAY(CURRENT DATE)

The first day of the previous month:

 LAST_DAY(CURRENT DATE - 2 MONTHS) + 1 DAY or (LUW) TRUNCATE(CURRENT DATE - 1 month, 'MONTH')

The first day of the current month:

 LAST_DAY(CURRENT DATE - 1 MONTH) + 1 DAY or (LUW) TRUNCATE(CURRENT DATE, 'MONTH');
0
source share

More articles:


All Articles