0

I used SELECT TO_DATE(INC_DATE, 'MMDDYYYY') FROM BASICINCIDENT2010;but since not all the dates in the inc_date are of the same format. It causes the error of Month not found.

Sourabh Gujar
  • 3
  • 1
  • I'm not positive I know what your question is. Maybe provide some data and what you want the output to be. – Matt Cremeens Jun 22 '16 at 17:50
  • @MattCremeens I think more important is the source data than output data. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Jun 22 '16 at 17:52

1 Answers1

0

You'll need to use a CASE statement to decode the date strings differently. Something like the following:

CASE
    WHEN LENGTH(inc_date) =  7 THEN TO_DATE(inc_date,  'MDDYYYY')
    WHEN LENGTH(inc_date) =  8 THEN TO_DATE(inc_date, 'MMDDYYYY')
    ELSE TO_DATE(inc_date, 'MM-DD-YYYY')
END AS fixed_date
Mr. Llama
  • 20,202
  • 2
  • 62
  • 115