Oracle to_date SQL formatting odd behavior

Question

I've trawled the SO website thinking this is pretty obvious but alas I have not found one yet. Basically the setup is as follows:

I have a string in a table like so:

06/22/2010 00:00:00

I do a select on it:

SELECT To_Date(item, 'MM/DD/YYYY HH24:MI:SS') from that_table

so that effectively I am doing

SELECT To_Date('06/22/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS') from that_table

My database returns:

22.06.2010 00:00:00

Now I know I can do a

to_char(to_Date('06/22/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'MM/DD/YYYY HH24:MI:SS')

but I need the select to return a date format (MM/DD/YYYY HH24:MI:SS), not a string. How can I do that?

Edit: Real output:

22.06.2010 00:00:00

Desired output (in Date format):

06/22/2010 00:00:00

Answer 1

Hi Rio,

a Date is a point in time, it has no format (like numbers: they don't have format). When you display the date, obviously it has to get a format: if you don't specifically give Oracle a format, the default format for your session will be used (session parameter NLS_DATE_FORMAT).

In consequence: use the DATE datatype for date arithmetics and storage (no format). Use the to_char function or something equivalent when you need to display the date (in a report or in the GUI).

Answer 2

A DATE doesn't have a format, it is only formatted when converted to a character string for display purposes. If you are using SQL Plus then when you display a DATE column (without using TO_CHAR to explicitly format it), SQL Plus itself performs a TO_CHAR conversion using the NLS_DATE_FORMAT setting:

SQL> select sysdate from dual;

SYSDATE
---------
23-JUN-10

SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
2010-06-23 15:17:12

If you are not using SQL Plus, then whatever tool you are using will be doing something similar and may have a different way to specify the date format for display.