|
View previous topic :: View next topic
|
| Author |
Message |
kishpra
New User
Joined: 24 May 2010
Posts: 92
Location: Pune
|
|
|
|
I have a select on below timestamp fiield name DATENS as
SELECT DATENS from Table;
I have the output as -
2014-02-07-08.55.33.982548
But I need the output in the below format in SELECT as
2014-02-07 08.55.33
i.e. YYYY-MM-DD HH:MM:SS |
|
| Back to top |
|
 |
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Try this SQL query. Replace CURRENT TIMESTAMP with your column name and SYSIBM.SYSDUMMY1 with your table name.
| Code: |
SELECT SUBSTR( CHAR(CURRENT TIMESTAMP),1,10)
|| ' ' || SUBSTR( DIGITS (HOUR(CURRENT TIMESTAMP)),9)
|| ':' || SUBSTR( DIGITS (MINUTE(CURRENT TIMESTAMP)),9)
|| ':' || SUBSTR( DIGITS (SECOND(CURRENT TIMESTAMP)),9)
FROM SYSIBM.SYSDUMMY1 WITH UR; |
Output:
| Code: |
COL001
#1
VARCHAR(19)
<---+----1----+--->
**** Top of data ****
2014-09-10 07:52:25
**** End of data **** |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Or try,
| Code: |
select replace(SUBSTR( CHAR(current timestamp),1,19),'.',':')
from sysibm.sysdummy1 |
|
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Hi Rohit,
This won't give him space between date and time part.
| Quote: |
2014-02-07 08.55.33
i.e. YYYY-MM-DD HH:MM:SS |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Mistah,
You are right, but I was thinking why it add '-' when we perform any function on timestamp. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
But may then below should work, so it up to TS which one to pick.
| Code: |
select SUBSTR(char(current timestamp),1,10)|| ' ' ||
REPLACE(SUBSTR(char(current timestamp),12,8),'.',':')
from sysibm.sysdummy1 |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
| varchar_format(current timestamp,'YYYY-MM-DD HH.MI.SS') |
|
|
| Back to top |
|
|
|
|
