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.