Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ankur.

Asked: March 11, 2021 - 8:27 am UTC

Last updated: March 15, 2021 - 5:07 am UTC

Version: 11.1.0.6.0

Viewed 1000+ times

You Asked 

--This works fine.
Select To_Date('11, Mar-2021', 'DD, Mon-YYYY')
From Dual;

--While this doesn't work even if I provided the correct 
--format string too. In this I just used "th".
Select To_Date('11th, Mar-2021', 'DDth, Mon-YYYY')
From Dual;


May I know why is the 2nd query raising error whereas Oracle says that to_date function can convert the literal date string if one provides proper format model and I did exactly the same and on top of that I didn't even used "fx".

Thanks.

and Chris said...

From the docs:

Datetime suffixes are valid only to format output

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Format-Models.html#GUID-515DFB76-E853-432F-BFEC-F1C62306BEC5

So the TH mask is only for converting a date to a string for display purposes. Not taking a string and converting it to a date!

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

A reader, March 12, 2021 - 5:21 pm UTC

Thanks a lot,I also tried to find it in on internet but unfortunately couldn't find it that time, thanks once again.
Connor McDonald
March 15, 2021 - 5:03 am UTC

glad we could help

Quoted text

Anton, March 13, 2021 - 10:21 am UTC

But if you have a input string containing those suffixes you can treat them as a "quoted text". This will work:

Select To_Date('11th, Mar-2021', 'DD"th", Mon-YYYY')
From Dual;
Connor McDonald
March 15, 2021 - 5:07 am UTC

Nice input
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.