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

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
Asked:
volking
  • 2
  • 2
1 Solution
 
Kent OlsenData 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
 
volkingAuthor 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
 
Kent OlsenData Warehouse Architect / DBACommented:
Oops.  :)

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

Hey -- it FEELS like Monday.  :)


Kent
0
 
volkingAuthor 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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