ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Get System Time as HHMMDD, with 0 Placeholder

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Get System Time as HHMMDD, with 0 Placeholder

    Hi, I want to update a table using SQL, and put the system time in a 6,0 field.

    My problem is if the minutes or seconds are between 1-9, I don't get a leading 0

    So 4:07:06 pm returns 1676, but I want 160706

    I have tried various formatting, concat, substring of CURRENT_TIME etc, but no luck yet

    example

    EXTRACT(HOUR FROM CURRENT_TIME) || EXTRACT(MINUTE FROM CURRENT_TIME) || EXTRACT(SECOND FROM CURRENT_TIME) AS HHMMSS
    Last edited by MFisher; July 23, 2020, 02:20 PM.

  • #2
    There has to be a better way but ...
    select ( EXTRACT(HOUR FROM CURRENT_TIME) * 10000)
    + ( EXTRACT(MINUTE FROM CURRENT_TIME) * 100 )
    + EXTRACT(SECOND FROM CURRENT_TIME) AS HHMMSS from sysibm.sysdummy1;
    Produces the right answer - albeit with a trailing decimals which you can dump.

    Comment


    • #3
      Knew there had to be a better way ...

      select ( cast (CURRENT_TIME as int) ) as hhmmss from sysibm.sysdummy1;

      That seems to produce exactly what you want directly.

      Comment


      • #4
        Thanks !

        Comment

        Working...
        X