469,600 Members | 2,281 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,600 developers. It's quick & easy.

Striping the time of the timestamp

Hi,
I want to change this query SELECT current timestamp FROM
sysibm.sysdummy1;
so it returns 2005-11-09-00.00.0.000000 not 2005-11-09-15.31.33.132000.
Basically stip the time of the timestamp
Thanks
-R

Nov 12 '05 #1
7 7253
In article <11*********************@f14g2000cwb.googlegroups. com>,
Ross (ro*********@gmail.com) says...
Hi,
I want to change this query SELECT current timestamp FROM
sysibm.sysdummy1;
so it returns 2005-11-09-00.00.0.000000 not 2005-11-09-15.31.33.132000.
Basically stip the time of the timestamp
Thanks
-R


select char(current date, ISO) concat '-00.00.00.000000' FROM
sysibm.sysdummy1;

or values char(current date, ISO) concat '-00.00.00.000000'
Nov 12 '05 #2
Ross wrote:
Hi,
I want to change this query SELECT current timestamp FROM
sysibm.sysdummy1;
so it returns 2005-11-09-00.00.0.000000 not 2005-11-09-15.31.33.132000.
Basically stip the time of the timestamp
Thanks
-R


Try SELECT DATE(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY
This will give you 2005-11-09
Nov 16 '05 #3
If you are trying to strip the time, as Paul said, the correct FUNCTION
would be DATE(), so SELECT DATE(CURRENT TIMESTAMP) FROM
SysIBM.SysDummy1;

If you are trying to round the day down (a datetime equivalent of
FLOOR) it would be a TIMESTAMP():SELECT TIMESTAMP(DATE(CURRENT
TIMESTAMP), '0.00') FROM SysIBM.SysDummy1;

Two notes though:

1) CURRENT DATE should work just as well.
2) SELECTing FROM SySIBM.SysDummy1 is unrequired as the VALUES keyword
should work just as well, and have no rights issues.

B.

Nov 16 '05 #4
In article <11**********************@g47g2000cwa.googlegroups .com>,
Ma***********@ThePentagon.com says...
If you are trying to round the day down (a datetime equivalent of
FLOOR) it would be a TIMESTAMP():SELECT TIMESTAMP(DATE(CURRENT
TIMESTAMP), '0.00') FROM SysIBM.SysDummy1;


I just noticed the timestamp_iso function which does exactly what the OP
wants:

C:\Temp>db2 values timestamp_iso(current date)

1
--------------------------
2005-11-16-00.00.00.000000

1 record(s) selected.
Nov 16 '05 #5
The OP gave an example of passing a timestamp, not a date.

Other than that... :)

B.

Nov 16 '05 #6
In article <11**********************@f14g2000cwb.googlegroups .com>,
Ma***********@ThePentagon.com says...
The OP gave an example of passing a timestamp, not a date.

Other than that... :)

B.

He wanted to return '2005-11-09-00.00.0.000000' instead of '2005-11-09-
15.31.33.132000' so I guess the datetime_iso is exactly what he needs.
Nov 16 '05 #7
I see. I was looking at the method, you're looking at the results.

B.

Nov 16 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by dan glenn | last post: by
2 posts views Thread by zaceti | last post: by
3 posts views Thread by Stanley Sinclair | last post: by
2 posts views Thread by Houston | last post: by
5 posts views Thread by js | last post: by
8 posts views Thread by johkar | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.