How do I convert a varchar field into the datetime data type?

Question

SELECT * FROM `travail` WHERE Convert(datetime, `dateReception`)=(datetime, '01/01/2014' )

The value of dateReception is 2014-01-01 but it's a varchar not date

Saharsh Shah · Accepted Answer · 2014-01-18 06:11:15Z

3

You can use STR_TO_DATE() function:

SELECT * FROM travail WHERE DATE(dateReception) = STR_TO_DATE('01/01/2014', '%d/%m/%Y')

answered Jan 18, 2014 at 6:11

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

He said that dateReception is in YYYY-MM-DD format, not DD/MM/YYYY format

Chirag Parekh · Accepted Answer · 2014-01-18 06:10:10Z

If your varchar is like this:

8/11/2011 20:14 PM

Then you can do something like this:

SELECT DATE_FORMAT(STR_TO_DATE('8/11/2011 20:14 PM', '%c/%e/%Y %H:%i'), '%Y-%m-%d %H:%m:%s')