Solved

iSeries DB2 -- CURRENT_TIMESTAMP -> Decimal(14) in SQL

Posted on 2007-11-28
4
8,451 Views
Last Modified: 2013-12-06
I am configuring a tool that, at a particular point, executes a SQL statement on a DB.  It is that generic ... my SQL statement can be anything.

I need a SQL statement that will put the current date / time into a particular field of a DB2 table on an AS400 / iSeries system.

I have Update TheTable Set TheField = CURRENT_TIMESTAMP Where ...

That errors out, however b/c TheField is of type DECIMAL (14).  My customer uses those for date /times.  The date / time would be encoded as 20071126143422

That outfit's programmer says I should generate the number using:
YEAR(CURRENT TIMESTAMP)*10000000000+
MONTH(CURRENT TIMESTAMP)*100000000+
DAY(CURRENT TIMESTAMP)*100000+
HOUR (current timestamp)*10000+
MINUTE(current timestamp)*100+
SECOND(current timestamp)

Is there no better way?  That is extremely unwieldy.

Thanks.
0
Comment
Question by:Daniel Wilson
[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
  • 2
4 Comments
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 100 total points
ID: 20365773
Hi DanielWilson,

Dates on the AS/400 version of DB2 are just a bit different than those on LUW.  Converting the value to a decimal integer is just silly, but if that's the rule that you live by, you don't have a lot of choice.

That block of code should work just fine.  As you say, it's unwieldy, but it should work.  If you're going to do this in several places in your SQL, write the SQL into a function so that the ugly portion hides from view.

Then your SQL is just:

SELECT timestamptoint (current timestamp);


Good Luck,
Kent
CREATE FUNCTION timestamptoint (timestamp intime)
  RETURNS decimal (14) 
  DECLARE returnvalue decimal(14); 
  SELECT 
    YEAR(CURRENT TIMESTAMP)*10000000000+
    MONTH(CURRENT TIMESTAMP)*100000000+
    DAY(CURRENT TIMESTAMP)*100000+ 
    HOUR (current timestamp)*10000+
    MINUTE(current timestamp)*100+
    SECOND(current timestamp)
  INTO returnvalue
  FROM sysibm.sysdummy1; 
  RETURN returnvalue
END

Open in new window

0
 
LVL 18

Accepted Solution

by:
Dave Ford earned 400 total points
ID: 20366511

The following works for me:

select decimal(left(replace(replace(char(current timestamp),'-',''),'.',''),14))
from   sysibm/sysdummy1

HTH,
DaveSlash
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 20367045
Dave, yours solved it for me.

KDO, I like the function idea, but was unable to get it to work ... possibly b/c of permissions, I'm not sure.

Thank you both.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 20367569
Hi daveslash,

Ah...  Good idea, Dave.

I've used similar countless times.  Don't know why it didn't just pop into my head.    :)    (Maybe it's already full?)


Kent
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

Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Suggested Courses

627 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