472,145 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

Convert Current Time to Dec


This should be simple, but brain is hurting...

How do I convert a Current Time to a Decimal 6,0 (HMS)? There must be a
cleaner way then this:

Insert into Table Values
Substr(Char(CURRENT TIMESTAMP),12,2) ||
Substr(Char(CURRENT TIMESTAMP),15,2) ||
Substr(Char(CURRENT TIMESTAMP),18,2)



Nov 12 '05 #1
8 17750
Take a peak at HOUR(), MINUTE(), and SECOND().

DEC() can also be used directly on the timestamp.

integer(dec(current timestamp) - dec(current date) * 1000000)


round(dec(current timestamp) - dec(current date) * 1000000, 0)

Nov 12 '05 #2
Simply use DECIMAL function.

------------------------- Commands Entered -------------------------
VALUES (Current Time, DECIMAL(Current Time));

1 2
-------- --------
23:08:27 230827.

1 record(s) selected.

Nov 12 '05 #3
Looking in your example, you want to convert from Current Timestamp
(NOT from Current Time).
So, first convert to Time from Current Timestamp, then apply DEC

------------------------- Commands Entered -------------------------
VALUES (Current Timestamp, DECIMAL(TIME(Current Timestamp)));

1 2
-------------------------- --------
2005-08-19- 231415.

1 record(s) selected.

Nov 12 '05 #4
Gosh I wish that worked. I should note, I'm on an iSeries DB2 UDB

Trying: Insert into TABLE values(DECIMAL(Current Time)) or
Insert into TABLE values(DECIMAL(Current Time), 6,0)
gives me.

An operand has been specified for the arithmetic function or operator
DECIMAL that is not valid. -- User-defined types cannot be specified as
operands of operators or scalar functions. User-defined types can only
be specified with operators and within user-defined functions created
specifically for that type. -- The operand of DIGITS can be any numeric
or numeric compatible type except floating-point. -- The operand of
be date, time, or timestamp. -- The other functions or operators
require numeric or numeric compatible operands. Recovery . . . :
Ensure all operands of function or operator DECIMAL are valid. Correct
the operands. Try the request again.

Nov 12 '05 #5
Looked at Hour/Min/Sec, so is this the cleanest why?

SELECT Dec(Hour(Now()) || Minute(Now()) || Second(Now()))


Nov 12 '05 #6
If it is the cleanest, i don't know, i was just offering an alternative.

Nov 12 '05 #7
I have no iSeries test environment. So, following example was only
checked by iSeries manual DB2 UDB SQL Reference(V5R2).
(This works on DB2 UDB for LUW).

SELECT Current Timestamp
, TIME(Current Timestamp) - '00:00:00'

Nov 12 '05 #8
Cool, that works. Tx

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Tim Quon | last post: by
5 posts views Thread by ECVerify.com | last post: by
4 posts views Thread by Gary Wessle | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.