|
View previous topic :: View next topic
|
| Author |
Message |
prav_06
Warnings : 1
Active User
Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands
|
|
|
|
Hi All,
I need to find the previous month's date, say suppose todays date is 01-01-2008 (1st Jan 2008), My query shd give an output like 01-12-2007, There is an option to get current date from SYSIBM.SYSDUMMY1, am planning to use this query to extract the previous month's date, please let me know the query by which I can do the same. Thanks in Advance
Thamilzan. |
|
| Back to top |
|
 |
prav_06
Warnings : 1
Active User
Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands
|
|
|
|
Hi All,
I think the below query works
SELECT CURRENT_TIMESTAMP - 1 MONTH
FROM SYSIBM.SYSDUMMY1;
please correct me if am wrong
Thamilzan. |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
Yes..But it should have been
SELECT DATE(CURRENT_TIMESTAMP - 1 MONTH)
FROM SYSIBM.SYSDUMMY1; |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| say suppose todays date is 01-01-2008 (1st Jan 2008), My query shd give an output like 01-12-2007, |
I feel that Your "business needs" need better understanding
and the query reviewed accordingly!
working with dates is a murky field!
what if todays date is 31/03/007 ( 31st March 2007 )
I am always amazed by the carelessness with which people
work on dates..... |
|
| Back to top |
|
|
prav_06
Warnings : 1
Active User
Joined: 13 Dec 2005
Posts: 154
Location: The Netherlands
|
|
|
|
Enrico,
Valid point , but I considered the situation and pls find the query and its o/p below
| Code: |
select (current_timestamp - 9 month + 1 day) from sysibm.sysdummy1;
---------+---------+---------+---------+---------+---------+---------+-
---------+---------+---------+---------+---------+---------+---------+-
2007-03-01-13.28.20.151246
DSNE610I NUMBER OF ROWS DISPLAYED IS 1 |
and
| Code: |
select (current_timestamp - 9 month - 1 day) from sysibm.sysdummy1;
---------+---------+---------+---------+---------+---------+-------
---------+---------+---------+---------+---------+---------+-------
2007-02-27-13.32.52.450104
DSNE610I NUMBER OF ROWS DISPLAYED IS 1 |
The above queery has given the expected value and I believe it should give the 27th of feb 2007 when the foll query is run on 31st march
| Code: |
| Select (current_timestamp - 1 month) from sysibm.sysdummy1; |
Please correct me if am wrong
Thamilzan. |
|
| Back to top |
|
|
|
|
