Solved

Format Current Timestamp

Posted on 2004-09-30
4
2,489 Views
Last Modified: 2012-06-27
Is there a way to format a current timestamp when inserting it into DB2 through SQL.
0
Comment
Question by:gdlp2004
4 Comments
 

Author Comment

by:gdlp2004
ID: 12192200
All I want to do is take off the nanoseconds on the timestamp, so it is yyyy-mm-dd hh-mm-ss

pts increased bc of time constraints
0
 
LVL 13

Assisted Solution

by:ghp7000
ghp7000 earned 100 total points
ID: 12193124
you cannot strip off the nanoseconds from a timesatmp value while inserting into db2 tables that have columns defined as timestamps, the insert/update will be rejected if you try.
What you can do is add a default values, like 00000 if you wish, but be careful in doing this as later, when you want to select records and order by, you will not really get an order if two of the timestamps have the same day,hour,minutes
0
 
LVL 3

Accepted Solution

by:
lwilkin7 earned 200 total points
ID: 12194282
You can use this expression in any INSERT or UPDATE statement where tstamp_fld can be either a column name or host variable.  This will strip the microseconds and set them to zeros.

timestamp(substr(char(tstamp_fld),1,19) || '.000000')

Hope this helps,
Lloyd
0
 
LVL 1

Assisted Solution

by:kkpenumala
kkpenumala earned 100 total points
ID: 12236112
You can use the expression

TRUNC_TIMESTAMP(tstamp_fld,'SS') , where tstamp_fld be either a column or host variable.

This expression will make all your nanoseconds to ZEROES

Hope this is what you are expecting
Kishore
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now