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.

Answer 1

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:

1. Choose menu Tools > Preferences.
2. In the Preferences dialog, select Database > NLS from the left panel.
3. From the list of NLS parameters, enter "MM/DD/YYYY"
4. 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.