1

I am trying to insert data having datatype as nvarchar to a column having data type as date and oracle is doing the conversion wrongly. I tried couple of things and it isn't working. Could some please suggest an alternative. I am using oracle 12cc

The source column have the value in the format 31-DEC-99 but when I use to_date to convert it, the value becomes 0099-12-31.

select to_date(EFCTV_DT,'yyyy-mm-dd') from table1;
Aswin Ajai
  • 11
  • 2
  • Please show us how your insert that string to the date column. – GMB Dec 18 '20 at 10:27
  • 1
    To ensure that `to_date()` can create a date object with a 4-digit year the source must have a 4-digit year as far as i know. If I try to create a date object from the varchar `'51-01-01'` and use the pattern `'yyyy-mm-dd'` it will result into `'0051-01-01'` – procra Dec 18 '20 at 10:27
  • 1
    Plus I would _highly_ recommend using 4-digit year numbers if you're handling with dates from the 20th century to avoid the side effects of the Y2K-phenomenon – procra Dec 18 '20 at 10:29
  • Why on earth do you store `DATE` values as strings - and even `nvarchar`? – Wernfried Domscheit Dec 18 '20 at 20:53

1 Answers1

1

You need to use correct format as follows:

to_date(EFCTV_DT,'dd-mon-rr')

Whenever you use the year with two digits, You have two options available with you as follows:

  • rr - make the year falling in xx50-current_year-xx49
  • yy - make the year falling in xx00-current_year-xx99

so the current year is 2020 and if you will use the 95 as the year then you will get the following:

rr - 1995
yy - 2095
Popeye
  • 35,427
  • 4
  • 10
  • 31
  • In order to be independent from current user session `NLS_DATE_LANGUAGE` settings you should prefer `to_date(EFCTV_DT,'dd-mon-rr', 'NLS_DATE_LANGUAGE=American')` – Wernfried Domscheit Dec 18 '20 at 20:52