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

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.
LVL 32
Daniel WilsonAsked:
Who is Participating?
 
Dave FordConnect With a Mentor Software Developer / Database AdministratorCommented:

The following works for me:

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

HTH,
DaveSlash
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
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
 
Daniel WilsonAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.