Convert Current Time to Dec

[Chris]

Sorry,

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
Dec(
Substr(Char(CURRENT TIMESTAMP),12,2) ||
Substr(Char(CURRENT TIMESTAMP),15,2) ||
Substr(Char(CURRENT TIMESTAMP),18,2)
,6,0)

TIA,

Chris

[Brian Tkatch]

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)

or

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

[Tonkuma]

Simply use DECIMAL function.

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

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

1 record(s) selected.

[Tonkuma]

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
function.

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

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

1 record(s) selected.

[Chris]

Gosh I wish that worked. I should note, I'm on an iSeries DB2 UDB
(V5R3).

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
INTEGER, SMALLINT, BIGINT, FLOAT, DOUBLE, and DOUBLE_PRECISION cannot
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.

[Chris]

Looked at Hour/Min/Sec, so is this the cleanest why?

SELECT Dec(Hour(Now()) || Minute(Now()) || Second(Now()))
FROM SYSIBM/SYSDUMMY1

Chris

[Brian Tkatch]

If it is the cleanest, i don't know, i was just offering an alternative.

[Tonkuma]

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'
FROM SYSIBM.SYSDUMMY1

[Chris]

Cool, that works. Tx