SQL time and date formatting against dual table

1
votes

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.

2
sqloracleoracle11gdual-table
Note that a DATE in Oracle, such as SYSDATE, is really a DATETIME (accurate to nearest second). – Jeffrey Kemp

2 Answers

2
votes

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.

1
votes

Try:

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