SQL time and date formatting against dual table

Question

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.

Answer 1

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.

Answer 2

Try:

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