Francisco Cervera Francisco Cervera - 4 years ago 196
SQL Question

SQL: Casting text to date

I have a TEXT column of dates and need to convert them to dates, but the two methods I'm using are not working correctly. See below.

SELECT CAST("12/01/2009" as date);
12




This only returns the first digit before stoping at the '/'.

SELECT DATE("12/01/2009");
Returns nothing




I also tried CONVERT, but I'm using SQLite and it doesn't appear to support it as I'm getting a syntax error. Any suggestions on how to solve this?

Answer Source

Try using STR_TO_DATE function

SELECT STR_TO_DATE('12/01/2009','%m/%d/%Y');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download