Oracle substitutes values from SYSDATE function when incomplete date is provided

Most likely you may have this situation on the exam. It is incomplete date format used with TO_DATE function. How will Oracle behave?

Let’s consider an example when we are trying to get a date value from Oracle using TO_DATE function while having only one single value instead of three. Normally you are supposed to have three values (Day, Month and Year) while working with TO_DATE. An example below shows us that Oracle applies a value supplied to a mask, the rest is taken from SYSDATE function. If we supply 12 to TO_DATE function and specify DD as a mask, then MON and YY masks are missing. But don’t worry, Oracle won’t return an error. It will substitute missing values from SYSDATE, therefore NOV will be returned as MON and 17 will be returned as YY

Now, let’s try to specify MM as a mask. As we can see below, Oracle will treat 12 as a DEC ( December) while everything else will be substituted from SYSDATE. The current year which is 2017 will be substituted and presented as 17. Day equals 1, despite the current day is 17

Now, YY mask will be used. Means we will get a Year equals 2012, and YY=12. The month will also be substituted. The current Month is November, therefore NOV is shown. Day equals 1, despite the current day is 17

Leave a comment

Your email address will not be published. Required fields are marked *