Solved

Calc Last-Day of MM/YYYY?

Posted on 2008-10-09
4
472 Views
Last Modified: 2013-12-06
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
Comment
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
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
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

by:volking
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

by:
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

by:volking
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
Just about everyone has an old PC laying around.  Ask anyone in the IT industry, whether they are a professional or play in it as a hobby.  From outdated Desktops to cheap "throwaway" laptops, they are all around and not as hard to "fix up" as you m…
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.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question