Oracle. How to output date and time?

You can also set a format that applies to all dates like this:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH:MI:SS PM';

That way, your original query would output the dates in the format you're after, without using TO_CHAR. To set back to the usual default format, just do this:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';

that is the oracle date format that is set as the default for your instance.

you should properly specify the format to see more or less.. something like this:

select to_char( datevisit, 'dd-mon-yy hh24:mi:ss' ) from visit

Oracle internally follow 'DD-Mon-YY' format to store in database. So it returns'DD-Mon-Y

If you want to date format with hours min and sec. you can alter NLS_DATE_FORMAT in session.

If you want to query for the just Presentation purpose for that instance. Use TO_char Function to convert into required format.

SELECT slotnum, TO_CHAR (datevisit, 'DD-MON-YY ') "DATEVISIT",
       TO_CHAR (actualarrivaltime, 'DD-MON-YY HH:MI:SS AM') "ACTUALARRIVALTIME"
  FROM visit

I Hope the above query gives you the output as you like.

Tags:

Time

Oracle

Date