• Status: Solved
• Priority: Medium
• Security: Public
• Views: 486

# Calc Last-Day of MM/YYYY?

using SQL for AS400 iSeries R5V3 ...
What is the easiest way to calculate the last-day of a particular month?

For Februray ...
for Feb'2008 that would be 02/29/2008? (a leap year)
but for Feb'2007 that would be 02/28/2007 (not a leap year)?
0
volking
• 2
• 2
1 Solution

Data Warehouse Architect / DBACommented:

Hi volking,

Given that you have a date within a month and want to find the last day of that month, it's pretty easy (and slick).

SELECT somedate + 1 month - day (somedate) FROM sysibm.sysdummy1;

Good Luck,
Kent
0

Author Commented:
@Kent

hmmmm ... guess I don't understand ...
I tried ...

SELECT (date('02/04/2008')) + (1 month) - (day (date('02/04/2008'))) FROM sysibm.sysdummy1;

and got error ...
SQL State: 42816
Vendor Code: -182
Message: [SQL0182] A date, time, or timestamp expression not valid. Cause . . . . . :   One of the following has occurred: -- An operand of addition is a date and the other is not a date duration. -- An operand of addition is a time and the other is not a time duration. -- An operand of addition is a timestamp and the other is not a duration. -- An operand of subtraction is a date and the other is not a date, character, or date duration. -- An operand of subtraction is a time and the other is not a time, character, or time duration. -- An operand of subtraction is a timestamp and the other is not a timestamp, character, or duration. Recovery  . . . :   Correct the arithmetic expression so that it contains a valid date, time, or timestamp expression. Try the request again.

0

Data Warehouse Architect / DBACommented:
Oops.  :)

SELECT somedate + 1 month - day (somedate) DAYS FROM sysibm.sysdummy1;
^^^^

Hey -- it FEELS like Monday.  :)

Kent
0

Author Commented:
@Kent .... COOL!

Works great ....
SELECT date('02/04/2008') + 1 month - day (date('02/04/2008')) DAYS FROM sysibm.sysdummy1;

Thanks!

P.S. MONDAY? No way man! It's Thursday! There's only FRIDAY between me and ... THE WEEKEND!!!!!!
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.