View previous topic :: View next topic
|
Author |
Message |
vab987
New User
Joined: 01 Dec 2006 Posts: 29 Location: S.A
|
|
|
|
Hi all,
I have to calculate the start date using DB2 if the time period is given.
For example, if somebody stayed at a location for 4 years and the left the location today ie 08-08-2008 , I have to get the date from when he started living at that location.
In this case, the answer (starting date) will be 08-08-2004.
I have been told by my seniors that there are some DB2 functions that can help you to get the start date if time period is given ( with ending date).
Can someone please help me.
Thanks
Vaibhav Rastogi |
|
Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006 Posts: 355 Location: New York
|
|
|
|
Hi,
Using DAYS function calculate the number of days for the end date and deduct these number of days from the person stayed number of days. Again convert this scalar value into date using DATE function.
Might be this is not an optimized sloution. You can wait for other suggetions also.
Regards,
ksk |
|
Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
May be you can use
select end_date - x years... if you are using years to get the start date
select end_date - x months...if you are using months to get the start date
and KSK said..u can use days when u want to sustract days..
Thanks,
Prajesh |
|
Back to top |
|
|
vab987
New User
Joined: 01 Dec 2006 Posts: 29 Location: S.A
|
|
|
|
but there is some DB2 function that calculates Start date with eaze. It also takes care for Leap year.
Please let me know if someone knows . |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
vab987 wrote: |
but there is some DB2 function that calculates Start date with eaze. It also takes care for Leap year.
Please let me know if someone knows . |
Have you tried looking in the sql reference manual for date/time calculations. |
|
Back to top |
|
|
vab987
New User
Joined: 01 Dec 2006 Posts: 29 Location: S.A
|
|
|
|
I don't have that manual. Can you please send me the link so that i can check it there. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
The SQL manual is linked to from the forum. Click on the "Manuals" link at the top of the web page. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Here is the OS/390 and z/OS V7 SQL Reference
Start with 3.2 Scalar functions 3.2.17 DATE
Regarding the date functions, little has changed between V4 and V9.1
you can download this book to your pc for future reference |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
Back to top |
|
|
|