CODEWITHSUNDEEP
sqloracle-databaseselecttoad
m.edmondson
m.edmondson

Reputation: 30872

Oracle to_date() incorrect output

There must be a very simple answer, but I can't find it anywhere.

I have the following which is a section of my select statement:

       case when q.renewal_date is not null then
            to_date(q._renewal_date, 'DD/MM/YYYY')
       else
            to_date(w.END_DATE, 'DD/MM/YYYY')
       end END_DATE,

according to all of the docs I can find the MM should give the month in numbers however I'm getting results such as:

30-SEP-12
26-JUN-11
30-SEP-12

It's also interesting that they're hyphenated (-) and not with slashes (/).

So what's the reason for this and how do I achieve what I want?

Upvotes: 2

Views: 2057

Answers (3)

Eric Scott
Eric Scott

Reputation: 189

to_date converts a string to a date. The code you have is taking a string (q._renewal_date) in 'DD/MM/YYYY' format and converting it to a date. What you are seeing is the default rendering of the date field.

Depending on what type q._renewal_date is, you probably need to use a different conversion/formatting function.

Upvotes: 0

Ollie
Ollie

Reputation: 17538

As you are TO_DATEing the value it is stored by Oracle internally as a date. It is displayed back to you using your NLS_DATE settings value which i would assume are set to DD-MON-YY by default.

You can check with

SELECT *
  FROM v$parameter
 WHERE name = 'nls_date_format';

You'll need to either alter your NLS_DATE_FORMAT setting (either for your session or for the DB) or TO_CHAR the output to the format you want to see.

Upvotes: 1

Andrew
Andrew

Reputation: 27294

Assuming w.end_Date and q._renewal_date are actual dates, you want to to_char them, not to_date. At present I would say you are seeing the dates in the format specified by your NLS settings. (If they are not dates, you are converting them to dates, but still letting your NLS settings choose the format you view it in)

Upvotes: 6

Related Questions