Search code examples
sqloracledate-formattingoracle8i

What number formats would to_number(to_char(sysdate, 'ydddhh24mi')) produce?

We're transferring a legacy Oracle SQL application to a new platform and I cannot locate proper date format documentation.

What type of numbers would be produced by

SELECT to_number(to_char(sysdate, 'ydddhh24mi')) FROM dual

if sysdate were

  • January 3, 2015 at 6:04 AM
  • December 23, 2000 at 4:17 PM
Solution

  • TO_CHAR documentation for Oracle 8 is at http://docs.oracle.com/cd/A87861_01/NT817EE/index.htm; this leads to the format model documentation at http://docs.oracle.com/cd/A87861_01/NT817EE/server.817/a85397/sql_elem.htm#34512. Using the table there...

    y returns the last digit of the year.
    ddd returns the day of the year.
    hh24 returns the military hour of the time.
    mi returns the minute of the hour.

    For January 3rd, 2015 @ 6:04 AM this is 50030604.
    For December 23rd, 2000 @ 4:17 PM this is 03571617.