Convert timestamp to date data type

Question

SELECT to_date(to_char(SYSTIMESTAMP, 'MM/DD/YYYY'), 'MM/DD/YYYY') FROM DUAL;

==> 04-MAR-16

Can anybody explain why this select statement doesn't result in '03/04/2016'?

How can I write my selection so that it does result in this, as a date type? I have also tried

SELECT to_date(to_char(trunc(SYSTIMESTAMP), 'MM/DD/YYYY'), 'MM/DD/YYYY') FROM DUAL

with the same result.

which environment/tool are you using to execute the query? – trincot Mar 04 '16 at 19:19 — trincot, Mar 04 '16 at 19:19
Oracle's sqldeveloper, version 4.1 – Brian Brock Mar 04 '16 at 19:23 — Brian Brock, Mar 04 '16 at 19:23
OK, I wrote an answer taking that into account. – trincot Mar 04 '16 at 19:30 — trincot, Mar 04 '16 at 19:30

score 3 · Accepted Answer · edited May 23 '17 at 10:28

When a date is returned by a query and displayed, it obviously needs to be formatted in some way. The way a date-type value is formatted is not determined by the query, but by the tool that executes your query and displays the result.

In the case of SQL developer you can set that format as follows:

Choose menu Tools > Preferences.
In the Preferences dialog, select Database > NLS from the left panel.
From the list of NLS parameters, enter "MM/DD/YYYY"
Save and close

See also this question.

Note that to convert a timestamp to date you need just to truncate it: trunc(SYSTIMESTAMP). Converting it to string and then back to a date is unnecessary.

score 0 · Answer 2 · answered Mar 04 '16 at 19:24

You are converting a datetime to a string and back to a date. Your system defaults the date format to DD-MMM-YY for output purposes; this is the normal default for date in Oracle.

It is important to understand that the internal data structure for date/time types has nothing to do with how they are presented. So, if you want it in another format, convert to a string using to_char().

If you want to change the default format, then look at NLS_DATE_FORMAT. The documentation is here.

Convert timestamp to date data type

2 Answers2