0

How do I convert two integers, one for date and one for time, into a timestamp?

  • Given 20200304 and 1046, return a timestamp for April 3, 2020, 10:46 AM.
  • Given 20200304 and 946, return a timestamp for April 3, 2020, 9:46 AM.
  • Given 20200304 and 2359, return a timestamp for April 3, 2020, 11:59 PM.
  • Given 20200304 and 0, return a timestamp for April 3, 2020, 0:00 AM.

I'm running Version 7, Revision 1.

  • Which version of DB2 for iSeries are you on? – mustaccio Apr 3 at 16:07
  • @mustaccio V7R1 – Sarov Apr 3 at 16:13
1

Try this:

WITH T (YYYYMMDD, HHMM) AS 
(
VALUES
  (20200304, 1046) -- return a timestamp for April 3, 2020, 10:46 AM.
, (20200304,  946) -- return a timestamp for April 3, 2020, 9:46 AM.
, (20200304, 2359) -- return a timestamp for April 3, 2020, 11:59 PM.
, (20200304,    0) -- return a timestamp for April 3, 2020, 0:00 AM.
)
SELECT 
  YYYYMMDD, HHMM
, TIMESTAMP_FORMAT(DIGITS(DEC(YYYYMMDD, 8)) || DIGITS(DEC(HHMM, 4)), 'YYYYMMDDHH24MI') AS TS 
FROM T;

The result is:

|YYYYMMDD   |HHMM       |TS                 |
|-----------|-----------|-------------------|
|20200304   |1046       |2020-03-04 10:46:00|
|20200304   |946        |2020-03-04 09:46:00|
|20200304   |2359       |2020-03-04 23:59:00|
|20200304   |0          |2020-03-04 00:00:00|
| improve this answer | |
  • Much better than my horrible brute-force hack. Thanks. – Sarov Apr 6 at 19:52
0

I managed to find a brute force solution, but I'm still hoping there's a better way:

TIMESTAMP(
    FLOOR(MYDATE / 10000)
    CONCAT '-'
    CONCAT MOD(FLOOR(MYDATE / 100),100)
    CONCAT '-'
    CONCAT MOD(MYDATE, 100)
    CONCAT ' '
    CONCAT CASE
        WHEN MYTIME < 100 THEN '00:' CONCAT MYTIME
        ELSE FLOOR(MYTIME/100) CONCAT ':' CONCAT MOD(MYTIME,100)
    END
    CONCAT ':00'
)
| improve this answer | |
0

An alternative might be

timestamp_format(
  substr(digits(mydate),3) || 
  substr(digits(mytime), 7), 'YYYYMMDDHH24MI');
| improve this answer | |
  • For me this query just returns '++++++++++++++' for all the timestamps. The one from Mark Barinstein worked, though. – Sarov Apr 6 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.