IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 Timestamp format


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
kishpra

New User


Joined: 24 May 2010
Posts: 92
Location: Pune

PostPosted: Wed Sep 10, 2014 4:59 pm
Reply with quote

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
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 310
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Sep 10, 2014 5:23 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2588
Location: NYC,USA

PostPosted: Wed Sep 10, 2014 8:36 pm
Reply with quote

Or try,
Code:
select replace(SUBSTR( CHAR(current timestamp),1,19),'.',':')
from sysibm.sysdummy1
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 310
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Sep 10, 2014 11:21 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2588
Location: NYC,USA

PostPosted: Thu Sep 11, 2014 12:11 am
Reply with quote

Mistah,

You are right, but I was thinking why it add '-' when we perform any function on timestamp.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2588
Location: NYC,USA

PostPosted: Thu Sep 11, 2014 12:23 am
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Sep 29, 2014 8:31 pm
Reply with quote

Code:
varchar_format(current timestamp,'YYYY-MM-DD HH.MI.SS')
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Printing a dataset in DUMP format wit... ABENDS & Debugging 2
No new posts Calculate with timestamp PL/I & Assembler 19
No new posts Converting a file from PD to display ... SYNCSORT 4
No new posts Date format conversion DFSORT/ICETOOL 4
No new posts Required Date Format in Include Sort ... DFSORT/ICETOOL 6
Search our Forums:

Back to Top