[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Format Current Timestamp

Posted on 2004-09-30
4
Medium Priority
?
2,495 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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

650 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