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
 

 

Return Timestamp as numeric value

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 183
Location: Toronto, ON, Canada

PostPosted: Wed Feb 23, 2011 12:37 am    Post subject: Return Timestamp as numeric value
Reply with quote

The below sql expression will return a Timestamp column as a pure numeric value ie. no dashes or periods. Is there an simplier way of doing this?

REPLACE(REPLACE(CHAR(timestamp_column), '-', ''), '.', '')
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Wed Feb 23, 2011 1:41 pm    Post subject:
Reply with quote

varchar_format(current timestamp,'YYYYMMDDHH24MISSNNNNNN')

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_bif_varcharformat.htm

also usefull for julian date, week, quarter,...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Feb 23, 2011 1:56 pm    Post subject:
Reply with quote

though the data is all numbers, without a CAST, it is still CHAR datatype.
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 183
Location: Toronto, ON, Canada

PostPosted: Wed Feb 23, 2011 7:36 pm    Post subject:
Reply with quote

GuyC wrote:
varchar_format(current timestamp,'YYYYMMDDHH24MISSNNNNNN')

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_bif_varcharformat.htm

also usefull for julian date, week, quarter,...


Unfortunately we are still using version 8 of DB2. It has the VARCHAR_FORMAT function but allows for only one string value 'YYYY-MM-DD HH24:MI:SS' which is silly. I guess I have to wait until we upgrade

FYI - I was looking for a CHAR format but with just numbers
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Feb 23, 2011 7:39 pm    Post subject:
Reply with quote

Code:
(timestamp_column + 1 year + 1 month + 1 day - '0001-01-01-00.00.00.000000')


You may have to change the format of the literal depending on your default date format.
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts Converting decimal to numeric using I... Balaryan DFSORT/ICETOOL 4 Thu Feb 02, 2017 11:27 pm
No new posts MAX-RC 88 and Return code 606 in DFH0... lind sh CICS 2 Tue Jan 24, 2017 1:23 pm
No new posts How to Capture the Max return code of... anilkumar922 All Other Mainframe Topics 2 Thu Jan 12, 2017 12:02 pm
No new posts DFHWS2LS return value divated CICS 4 Mon Nov 07, 2016 5:44 pm


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