CODEWITHSUNDEEP
oracle-databaseoracle11g
mvb13
mvb13

Reputation: 1584

Oracle to_date format issue

I have an insert statement, where one of the inserted fields is date. I use to_date function to convert string to date in this way:

to_date('10-MAY-10', 'DD-MON-RR')

It works fine, but I found, that it allows also variants like:

to_date('10?MAY?10', 'DD-MON-RR')
to_date('10+MAY+10', 'DD-MON-RR')

I'm expecting an Oracle error, however it makes an insert. Could you please explain why or give a reference to relevant documentation?

Upvotes: 1

Views: 159

Answers (1)

Boneist
Boneist

Reputation: 23578

Oracle will test for other formats if it fails to find a match in the string - you can see the rules for what it looks for here in the documentation.

As an aside, years have four digits. Please make sure you specify all four when you provide a date-as-a-string, where possible; it saves the database from having to guess and potentially getting it wrong. I.e. your original example should be:

to_date('10-05-2010', 'DD-MM-YYYY')

If you need to restrict the date-as-a-string to a specific format, you can use the fx format modifier, which is mentioned earlier in the same document I linked to previously.

eg. to_date('10/05/2010', 'dd-mm-yyyy') would match but to_date('10/05/2010', 'fxdd-mm-yyyy') would fail

Upvotes: 1

Related Questions