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
 
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: 187
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: 1281
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: 6970
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: 187
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 IBEGENER RETURN CODE 12 anilkumar922 All Other Mainframe Topics 5 Wed Dec 13, 2017 11:41 am
No new posts File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am
No new posts Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am
No new posts Return a Non Numeric value from REXX Learncoholic CLIST & REXX 3 Fri Sep 01, 2017 8:33 pm
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am

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