Skip to Main Content
  • Questions

  • Format a 6 digit number field in JD Edwards table as HH24:MI:SS

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Elizabeth.

Asked: May 13, 2019 - 2:31 pm UTC

Last updated: May 15, 2019 - 3:59 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

I have this value (170443) in a column which I would want to display as 17:04:43. I have written this piece of code to achieve this but seeing error ORA-01850: hour must be between 0 and 23. What is the solution?

SELECT CASE WHEN LVUPMT= 0 THEN NULL 
  ELSE
    TO_TIMESTAMP(substr(LVUPMT,1,2) ||':'||substr(LVUPMT,3,2) ||':'||substr(LVUPMT,5,2), HH24:MI:SS')
  END AS DDATE 
FROM PRODDTA.F07620 
WHERE LVAN8 = 102366;

and Chris said...

I'm not familiar with JD Edwards. So can't give specific help on that.

Passing 170443 and "it works for me":

select CASE WHEN '170443' = 0 THEN NULL 
  ELSE
    TO_TIMESTAMP(substr('170443',1,2) ||':'||substr('170443',3,2) ||':'||substr('170443',5,2), 'HH24:MI:SS')
  END AS DDATE
from dual;

DDATE                            
01-MAY-2019 17.04.43.000000000


So presumably you're returning rows with a different value...

You need to look and see what substr(LVUPMT,1,2) returns.

PS - If you just want to display the TIME, to_timestamp is the wrong function... This will convert the value to a timestamp with today's date and the time supplied:

select to_timestamp ( '12:34:56', 'HH24:MI:SS' ) , sysdate
from   dual;

TO_TIMESTAMP('12:34:56','HH24:MI:SS')   SYSDATE                
01-MAY-2019 12.34.56.000000000          14-MAY-2019 14:53:11


If this is just for display, just format the number...


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

JDEdwards stores times and dates as numbers....

Paul, May 15, 2019 - 1:59 pm UTC

Ensure your "upmt" column is 6 digits before trying to format it.

Here's an example with the address book(F0101). 

SELECT abupmt, 
  TO_TIMESTAMP(substr(abupmt,1,2) ||':'||substr(abupmt,3,2) ||':'||substr(abupmt,5,2), 'HH24:MI:SS')
FROM PRODDTA.f0101
WHERE 1=1
    and abupmt < 100000;

ORA-01850: hour must be between 0 and 23
01850. 00000 -  "hour must be between 0 and 23"
*Cause:    
*Action:

/* Now ensure time column is 6 digits */

SELECT abupmt, 
  substr(lpad(abupmt,6,'0'),1,2) ||':'||substr(lpad(abupmt,6,'0'),3,2) ||':'||substr(lpad(abupmt,6,'0'),5,2) AS jde_just_formatted,
  TO_TIMESTAMP(substr(lpad(abupmt,6,'0'),1,2) ||':'||substr(lpad(abupmt,6,'0'),3,2) ||':'||substr(lpad(abupmt,6,'0'),5,2), 'HH24:MI:SS') AS jde_timestamp     
FROM PRODDTA.f0101
WHERE 1=1
    AND abupmt < 100000
    AND rownum < 4;


    ABUPMT JDE_JUST_FORMATTED   JDE_TIMESTAMP
---------- -------------------- -----------------------------------
     22103 02:21:03             01-MAY-19 02.21.03.000000000 AM
     22336 02:23:36             01-MAY-19 02.23.36.000000000 AM
     92342 09:23:42             01-MAY-19 09.23.42.000000000 AM

3 rows selected.

Chris Saxon
May 15, 2019 - 3:59 pm UTC

Good point, thanks for sharing.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.