1

We have a below query like 

SELECT TO_DATE(CREATED_DATE_IN_CHAR,'MON-DD') FROM TABLE1

Data in the columns are in the same format of MON-DD.

on executing the above query we are getting ORA-01839: date not valid for month specified. On further analysis we found that created_date_in_char is having a value like 'FEB-29'. So oracle is trying to convert to the 'FEB-29' to '29-FEB-17' (current year is 2017) which is not a valid date in the year 2017.

Is it possible to make to_date function take a leap year for date conversion instead of current year (2017).

We are stuck here .Any help or suggestion is appreciated.

Hariharan
  • 21
  • 2
  • 1
    Hmmm...perhaps the best thing to do would be to control the date inputs going into your queries. If being done from the application layer, e.g. Android or maybe a mobile app, you could use a widget which present a correct calendar. – Tim Biegeleisen Aug 11 '17 at 06:29
  • @TimBiegeleisen Thanks for your input. But the requirement is to get the date in the same format from the application layer (UI). User can choose only MON-DD in the UI. – Hariharan Aug 11 '17 at 06:36

1 Answers1

0

You can't make to_date() assume a different year, but you can tell it to use a specific year (any year that is a leap year) by passing that in as part of the string - just concatenate a fixed value, and modify the format model to match:

SELECT TO_DATE('2016-' || CREATED_DATE_IN_CHAR,'YYYY-MON-DD') FROM TABLE1

Quick demo:

alter session set nls_date_format = 'YYYY-MM-DD'; -- for display only

with table1 (created_date_in_char) as (
  select 'JAN-01' from dual
  union all select 'FEB-29' from dual
  union all select 'DEC-31' from dual
)
SELECT TO_DATE('2016-' || CREATED_DATE_IN_CHAR,'YYYY-MON-DD') FROM TABLE1;

TO_DATE('2
----------
2016-01-01
2016-02-29
2016-12-31

It seems rather strange and unhelpful to store a created date (or any date) as just month and day, or any date as a string; but that isn't the immediate issue...

Alex Poole
  • 183,384
  • 11
  • 179
  • 318