?
Solved

Format Current Timestamp

Posted on 2004-09-30
4
Medium Priority
?
2,494 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 400 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 800 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 400 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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…
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 is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

764 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