ansaurus

Question

Select first day of preceding month in (DB2) SQL

Answer 1

A:

I believe the following will work for you.

ROUND_TIMESTAMP (somedate,'W')

moleboy 2009-06-30 13:09:38

I'm not seeing that function listed for DB2 for Linux, UNIX, and Windows platforms. It's probably available only for mainframe DB2.

Fred Sobotka 2009-07-01 00:06:54

My bad. strictly z/OS.It never ceases to amaze me how a company can release a single product and not have functionality as universal as possible across platforms. I can't imagine there is a single reason this wouldn't work everywhere. Outside of that, I don't know another solution other than what you've posted below OR breaking the current date down into string chunks MONTH, DAY, YEAR, modifying the MONTH and DAY, and then rebuilding the new date.

moleboy 2009-07-01 11:57:31

Shame, this did look perfect when I looked it up.

nearly_lunchtime 2009-07-01 12:14:37

I hear ya. There are features in DB2 z/OS that us LUW folks are just barely getting in DB2 9.7, and other things that aren't anywhere close to being ported. That said, IBM is making a lot of progress reconciling SQL and features between mainframe DB2 and DB2 LUW (which is sometimes referred to derisively by the mainframe crows as "little" DB2). Conference sessions and user group presentations by well-known DB2 speakers such as Craig Mullins are pointing out the ever-growing set of SQL that works identically in both DB2 products. It's still not perfect, but I've seen significant progress.

Fred Sobotka 2009-07-02 05:21:56

Hearing that makes me happy. Currently, I'm working with IBM's Maximo asset management system and IBM has been pretty lousy with it. I'm under the impression that it is supported primarily by the people who worked for the company IBM acquired it from. Its a product designed by people who don't see databases as anything more than (effectively) a series of flat files. And their support is miserable. If it ever gets folded into IBM's mainstream support, maybe I'll get lucky.

moleboy 2009-07-02 12:46:00

Answer 2

+4 A:

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

Fred Sobotka 2009-06-30 19:19:50

Nice, these are fairly painless. Thanks.

nearly_lunchtime 2009-07-01 12:16:34

Answer 3

A:

In order to flesh this out with more info. 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 was doing

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

Which wasn't giving me what I wanted, sometimes the date was the 30th instead of 31st for months with 31 days...

Changing it to

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

Gave me what I wanted. Looks like the first addition of the Months from the prev quarter date was resetting the DAY part of my date and thus the second addition of months was working on a date with only 30 days in the month.

Just something to be wary of when using this method of date manipulation in DB2.

MadMurf 2009-07-14 07:03:19

The most reliable way to produce the last day of a month is to build an expression that produces the first day of the month after it, and then subtract one day. Last day of next quarter, for example is DATE('0001-01-01') + YEAR(CURRENT DATE) YEARS - 1 YEAR + ((QUARTER(CURRENT DATE) +1) * 3) MONTHS - 1 DAY

Fred Sobotka 2009-07-15 17:06:23

Answer 4

A:

First Day of the Current Month:

CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS

First of the Current Year is

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

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

2009-09-30 19:56:03

ansaurus

tags:

views:

answers:

Select first day of preceding month in (DB2) SQL

related questions