Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 86
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: 268
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: 1609
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: 268
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: 1609
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: 1609
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: 1278
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 Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm
This topic is locked: you cannot edit posts or make replies. output data in CSV Format janmejay DFSORT/ICETOOL 3 Sat Oct 15, 2016 2:16 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us