1

I am confused with to_date(char[,'format'[,nls_lang]) function. Lets suppose I have to_date('31-DEC-1982','DD-MON-YYYY');should the format specified in the function be same as the date string? The above function works fine. When I use to_date('31-DEC-1982','DD-MM-YYYY'); also works fine but the month field in date string and that in format does not match. So my doubt is should the date string and format specified match exactly to convert it to Date object.

Thanks

ElGavilan
  • 6,610
  • 16
  • 27
  • 36
sandywho
  • 353
  • 1
  • 7
  • 16
  • 1
    Not answering your question, but why put up with those silly formats? Why not just use the SQL standard `DATE` literal: `DATE '1982-12-31'` – Lukas Eder Aug 14 '15 at 10:31
  • 1
    @LukasEder The ANSI date literal does not let you express a time component to a date - which is fine for the limited examples the OP gives but doesn't allow you to create the full range of times - for that you need the format models. – MT0 Aug 14 '15 at 13:53
  • @MT0: As you said. The OP isn't looking for time precision. But fine. How about `TIMESTAMP '1982-12-31 23:59:59'`, then? :) – Lukas Eder Aug 14 '15 at 13:56
  • @LukasEder The string is coming from something besides SQL Source, like a file. – Shannon Severance Aug 14 '15 at 16:06

2 Answers2

1

Generally speaking, yes, the date string and specified format should match. But the reason why it works in your case is that Oracle, for certain cases, provides flexibility of alternative format matching.

Excerpt from official Oracle Site

If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements

Alternative Formats

So as per above table, you can use 'MON' or 'MONTH' in place of 'MM'. Similarly you can use 'YYYY' in place 'YY', etc

Reference:

Oracle Format Matching

Maulik Shah
  • 402
  • 1
  • 4
  • 18
0

Whatever format you follow, the object returned will be of date type. You can test this via creating a dummy table and showing the table description. e.g. CREATE TABLE TEST AS( select to_date('31-DEC-1982','DD-MON-YYYY') dd from dual);

Now desc test;

Result will be dd date. Similar will be the result with another type.

However if you are using SQL Developer, the date will be show in the exact NLS format as the setting there applies.

tools->preferences->database->NLS
debug_04
  • 47
  • 6