Solved

Format Current Timestamp

Posted on 2004-09-30
4
2,491 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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