| View previous topic :: View next topic |
| Author |
Message |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
 Posted: Thu Apr 09, 2009 4:08 pm Post subject: Begining of Month TIMESTAMP |
 |
|
I have query that will produce the first time stamp for the current month. Is it possible to get the same results without so many functions?
| Code: | select CURRENT timestamp -
(select DAY(CURRENT DATE)
FROM SYSIBM.SYSDUMMY1)days + 1 days -
(select hour(CURRENT time)
FROM SYSIBM.SYSDUMMY1)hour -
(select minute(CURRENT time)
FROM SYSIBM.SYSDUMMY1)minute -
(select second(CURRENT time)
FROM SYSIBM.SYSDUMMY1)second -
(select microsecond(CURRENT timestamp)
FROM SYSIBM.SYSDUMMY1)microsecond
FROM SYSIBM.SYSDUMMY1;
Returns:
2009-04-01 00:00:00.000000
|
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Thu Apr 09, 2009 4:39 pm Post subject: |
|
|
NASCAR9,
isnt it simple like this? You can use TIMESTAMP(Date,TIME) function to get the desired results.
To get the first of the current month ,take the current date and subtract a month from and use LAST_DAY function to get the last day of prior month and then add 1 day to it which would give you the first day of the current month.
| Code: |
SELECT TIMESTAMP(LAST_DAY(CURRENT DATE - 1 MONTH) + 1 DAY,
TIME('00:00:00'))
FROM SYSIBM.SYSDUMMY1;
|
Kolusu |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
|
| Posted: Thu Apr 09, 2009 4:42 pm Post subject: |
|
|
NASCAR9,
You can even use DAYOFMONTH function to get the first day of the month
| Code: |
SELECT TIMESTAMP(CURRENT DATE - DAYOFMONTH(CURRENT DATE) DAYS + 1 DAY,
TIME('00:00:00'))
FROM SYSIBM.SYSDUMMY1;
|
Kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Thu Apr 09, 2009 4:47 pm Post subject: |
|
|
kolusu, I guess I just used a backhoe when I only needed a shovel. Thanks!
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
