Search code examples
sqloracle-databaseoracle11gdual-table

SQL time and date formatting against dual table

I am trying to only display the date and time of a table in a certain format. This format is DD-MON-YYYY and the time HH24:MI:SS. I don't understand how to make both formats work together. I can get them to function separately.

select to_char(sysdate, 'DD-MON-YYYY', systimestamp,'HH24:MI:SS') from dual;

My error is 'too many arguments'. I want to understand why it isn't working.

Solution

  • From the documentation TO_CHAR takes three arguments when using dates

    • a date or date time
    • a format model

    • optional NLS parameter for the localization You can concatenate the two results together with this.

      select to_char(sysdate, 'DD-MON-YYYY')||' '|| TO_CHAR(systimestamp,'HH24:MI:SS') from dual;

    But why would when you do it one call

    SELECT TO_CHAR(systimestamp,'DD-MON-YYYY HH24:MI:SS') from dual;

    NB SQL is not case sensitive in regards to keywords. Upper or lower case both work.