By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,778 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,778 IT Pros & Developers. It's quick & easy.

Convert Current Time to Dec

P: n/a

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
Share this Question
Share on Google+
8 Replies

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
Looked at Hour/Min/Sec, so is this the cleanest why?

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


Nov 12 '05 #6

P: n/a
If it is the cleanest, i don't know, i was just offering an alternative.

Nov 12 '05 #7

P: n/a
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

P: n/a
Cool, that works. Tx

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.