0

I am trying to convert DD-MON-RR to YYYY-MM-DD format in oracle and I have written the below conversion and it works fine in SQL Developer.

TO_CHAR(TO_DATE(date,'DD-MON-RR'),'YYYY-MM-DD')

However, if i use the same query in SSIS it throws a ORA- 01861 Literal does not match the format string.

Any help is appreciated!!

enter image description here

Improve this question
6
  • If the date part is already a date then don't call to_date() for it; just do TO_CHAR(date,'YYYY-MM-DD'). See this answer for an explanation. Commented Jul 22, 2021 at 10:46
  • Hi @AlexPoole, Actual problem is I have a date column which has a value 1-Oct-58. This a date of birth column which mean it is 1st of October 1958. When i move this data into SQL Server it is getting loaded as 01-10-0258. So i am doing a conversion as above and it works in SQL Developer but gives ORA-01861 error in SSIS. Commented Jul 22, 2021 at 10:52
  • If you move it as a date then that shouldn't happen. If you want it as a string in YYYY-MM-DD format then just use to_char(). Commented Jul 22, 2021 at 10:56
  • Data in the column - 20-JUL-58 Conversion : TO_CHAR(TO_DATE(date,'DD-MON-RR'),'YYYY-MM-DD')) Output : 1958-07-20 If i do it as date: TO_DATE(date,'DD-MON-RR') Output: 20-JUL-58 as char YYYY-MM-DD TO_CHAR(date,'YYYY-MM-DD') Output : 0258-07-20 Commented Jul 22, 2021 at 11:15
  • If the column is a date then 20-JUL-58 is just how your client is displaying the value. Oracle has its own internal representation. Please read previous comments and the old answer I linked to (at least the first half). You only need TO_CHAR(date,'YYYY-MM-DD'). If that gives you 0258-07-20 then your date is actually in year 258 - probably from a similar confusion of function calls, implicit conversion and NLS settings when it was inserted. Commented Jul 22, 2021 at 11:20

1 Answer 1

Reset to default
0

TO_CHAR(date,'YYYY-MM-DD') Output : 0258-07-20

That means the date stored in your database is actually in year 258, not 1958.

When you do

TO_CHAR(TO_DATE(date,'DD-MON-RR'),'YYYY-MM-DD')

you are implicitly converting your date to a string using your current session NLS date format, which is giving you a string like '20-JUL-58'. That is then explicitly converted back to a date using format mask 'DD-MON-RR'.

You are effectively doing:

TO_CHAR(TO_DATE(TO_CHAR(date,<nls_date_format>),'DD-MON-RR'),'YYYY-MM-DD')

which in your SQL Developer session is effectively:

TO_CHAR(TO_DATE(TO_CHAR(date,'DD-MON-RR'),'DD-MON-RR'),'YYYY-MM-DD')

Because the intermediate string has a 2-digit year (58) and you have the RR element, that becomes 1958.

When you do the same thing from SSIS the session date format appears to have a 4-digit year, so there it's effectively doing something like:

TO_CHAR(TO_DATE(TO_CHAR(date,'DD-MM-YYYY'),'DD-MON-RR'),'YYYY-MM-DD')

Now the intermediate string has a 4-digit year (0258) and with the RR element that stays as 0258.

Now.. you could specify the intermediate string format instead of relying on NLS, which would at least then be consistent between the two sessions:

TO_CHAR(TO_DATE(TO_CHAR(date,'DD-MON-RR'),'DD-MON-RR'),'YYYY-MM-DD')

Or to have SSIS treat is as a date not a string remove the outer to_char():

TO_DATE(TO_CHAR(date,'DD-MON-RR'),'DD-MON-RR')

Also note that this conversion ends up with the time part of the value at midnight; if your source dates have non-midnight times (probably not as it's a date-of-birth according to comments) and you want to keep those you should include the time in the mask:

TO_DATE(TO_CHAR(date,'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS')

But this double conversion is a nasty workaround for bad data, and really you should fix the data in your source table to have the correct year, and look at how values are being inserted to prevent them going wrong again.

Depending on exactly how and why they are wrong - probably an incorrect or implicit format mask and/or unexpected NLS settings during insert - you may need to assess whether you can rely on the last two digits of the year being correct and matching the RR element behaviour. And maybe whether you can rely on the month and day part being correct.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.