CODEWITHSUNDEEP
oracle-databasedate
Harshrossi
Harshrossi

Reputation: 287

Oracle TO_DATE() function gives undesired output

I have a DATE column with Oracle timestamp datatype in database

It stores values as such:

06-SEP-13 05.22.23.000000000 PM

I try to do this TO_CHAR(DATE, 'dd-MM-yyyy') and I get this: 06-09-2013.

then I try to convert the output to date using TO_DATE,

TO_DATE(TO_CHAR(DATE, 'dd-MM-yyyy'), 'dd-MM-yyyy')

but I get this: 06-SEP-13.

I am expecting it to be in this format 06-09-2013.

Why is this happening?

Help is much appreciated..

Upvotes: 0

Views: 529

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

to_date() doesn't give the date any format; dates and timestamps have an internal representation and don't have any meaningful/useful format until they are displayed somewhere. You're converting your timestamp into a string and then back into a date, which you coild also do with

cast(trunc(your_timestamp) as date)

where the trunc function removes the time portion, and the cast function converts the result from timestamp to date.

When your query displays that date, however you construct it, your client decides what format to use. If you don't explicitly specify a format with to_char() then your client will use a default, usually NLS_DATE_FORMAT. You can change that for each session, but you should not rely on implicit settings that might be different when someone else runs the code, so always use to_char for display.

Upvotes: 3

Veerender
Veerender

Reputation: 97

What tool are you using? I am sure it's NLS date format session setting of it.

Upvotes: 2

Related Questions