CODEWITHSUNDEEP
mysqlsqldategroup-by
Kukiwon
Kukiwon

Reputation: 1332

Group-by date, given a varchar (string)

I am given a database with a table that contains the attribute "datum" (varchar(25)). Because of poor database design, this column is filled with date values in string format, for example:

'February 25, 2013, 3:47 p'

I need to query the table and group all rows by day (so 25 in the example above). Should I be using the DATE_FORMAT(datum, '') function to be able to do this? Whenever I use that function, I get null values.

Upvotes: 1

Views: 599

Answers (2)

Matekk
Matekk

Reputation: 683

Have you tried STR_TO_DATE() function (documentation)?

It is the inverse of DATE_FORMAT() which takes a date value as its first parameter, not a string. You can then use that to get the correct day values.

Here is an SQL command that matches your query, I tested and works.

SELECT * 
FROM tablename 
WHERE DATE_FORMAT( STR_TO_DATE(datum,'%M %e, %Y, %h:%i %p'), '%d' ) = 25;

Upvotes: 2

John Woo
John Woo

Reputation: 263683

Since it is a string, you should convert it to a valid date value using STR_TO_DATE

SELECT  *
FROM    tableName
WHERE   STR_TO_DATE(datum,'%M %e, %Y, %h:%i %p') ....

The difference between the two:

STR_TO_DATE - converts string to date
DATE_FORMAT - converts date to string

Upvotes: 2

Related Questions