Solved

Calc Last-Day of MM/YYYY?

Posted on 2008-10-09
4
470 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
  • 2
  • 2
4 Comments
 
LVL 45

Expert Comment

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows 7/OS X dual boot 10 75
windows 10 free update 45 132
Tablets in POS (point of sale) environment 5 173
IBMi supported MySQL versions. 2 48
I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
#Citrix #POC #XenDesktop #vCenter #VMware #ESX
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.

770 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