Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 Timestamp format in SELECT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 Timestamp format in SELECT
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: 287
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Sep 10, 2014 5:23 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1819
Location: NY,USA

PostPosted: Wed Sep 10, 2014 8:36 pm    Post subject:
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: 287
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Wed Sep 10, 2014 11:21 pm    Post subject: Re: DB2 Timestamp format in SELECT
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

Senior Member


Joined: 21 Sep 2010
Posts: 1819
Location: NY,USA

PostPosted: Thu Sep 11, 2014 12:11 am    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1819
Location: NY,USA

PostPosted: Thu Sep 11, 2014 12:23 am    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Format Last Record differently from r... Learncoholic DFSORT/ICETOOL 5 Tue Oct 24, 2017 12:01 pm
No new posts Format issues using XMITIP MSGDD ssdjp1 CLIST & REXX 5 Mon Oct 23, 2017 8:47 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us