Solved

# Calc Last-Day of MM/YYYY?

Posted on 2008-10-09
476 Views
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
Question by:volking
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 45

Expert Comment

ID: 22678422

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

LVL 5

Author Comment

ID: 22678496
@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

LVL 45

Accepted Solution

Kent Olsen earned 500 total points
ID: 22678541
Oops.  :)

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

Hey -- it FEELS like Monday.  :)

Kent
0

LVL 5

Author Comment

ID: 22678589
@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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally Windows/Microsoft Updates will fail to update. We have found a code that will delete all temporary files and re-register all dll's related to Windows/Microsoft Updates! This works 99% of the time to get the updates working again! The…
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
###### Suggested Courses
Course of the Month8 days, 6 hours left to enroll