CODEWITHSUNDEEP
sqlsqlite
FCervera
FCervera

Reputation: 15

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?

Upvotes: 0

Views: 565

Answers (3)

Ferit
Ferit

Reputation: 9657

SqLite doesn't have date type. You need to do string manipulation do achieve this.

Upvotes: 0

Pooja Khokhani
Pooja Khokhani

Reputation: 31

SELECT CAST('2009-01-12' AS DATE);

Use it. It returns 2014-02-28

Upvotes: -1

Pரதீப்
Pரதீப்

Reputation: 93694

Try using STR_TO_DATE function

SELECT STR_TO_DATE('12/01/2009','%m/%d/%Y');

Upvotes: 3

Related Questions