CODEWITHSUNDEEP
sqloracle-databasedate
Gaurav Soni
Gaurav Soni

Reputation: 6338

to_date function strange behaviour for specific year

Different behaviour ,when the date contains year 2000

 select to_date(add_months(sysdate,-50),'dd/mm/yyyy') from dual    --error

 ORA-01858: a non-numeric character was found where a numeric was expected

  -- 04/08/2000 12:59:15  contains year 2000

 select to_date(sysdate,'dd/mm/yyyy') from dual   --this work,but diff output

Upvotes: 1

Views: 253

Answers (2)

ram12393
ram12393

Reputation: 1258

Your first query select to_date(add_months(sysdate,-50),'dd/mm/yyyy') from dual works fine for me

Result:

TO_DATE(ADD_MONTHS(SYSDATE,-50),'DD/MM/YYYY')
---------------------------------------------
04-OCT-09

Upvotes: 0

valex
valex

Reputation: 24134

You should use TO_CHAR instead of TO_DATE.

The first parameter of TO_DATE function is a STRING parameter so here your data is converted to STRING representation and then converted to date format BUT in the DIFFERENT format:

to_char(add_months(sysdate,-50),'dd/mm/YYYY')

Upvotes: 2

Related Questions