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: 1278
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 How to Capture the Max return code of... anilkumar922 All Other Mainframe Topics 0 Thu Jan 12, 2017 12:02 pm
No new posts DFHWS2LS return value divated CICS 4 Mon Nov 07, 2016 5:44 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts Casting a Binary Number to Numeric in... Bob Steinkraus Java & MQSeries 6 Tue Aug 09, 2016 5:58 pm
This topic is locked: you cannot edit posts or make replies. Alphanumeric to Numeric move on UNIX mistah kurtz COBOL Programming 16 Wed Jul 27, 2016 8:47 pm


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