CODEWITHSUNDEEP
mysql
dlofrodloh
dlofrodloh

Reputation: 1744

How to compare a date when the date is stored as a varchar

The dates in my database are stored as varchars instead of date formats due to the way it was first built.

The dates look like this: e.g. 1/3/2015 and 10/3/2015

I'm trying: 

"SELECT COUNT(*) n FROM tracker WHERE TIMESTAMP(STR_TO_DATE(date, '%d/%m/%Y'))<=NOW()"

However, that's not working. It is returning the count of all records, regardless of the date.

How can I count only the records where the date is today or in the past?

Upvotes: 0

Views: 38

Answers (2)

Marcel Piquet
Marcel Piquet

Reputation: 168

You should pay attention to the functions STR_TO_DATE and NOW(), the first return a date, the second is a timestamp. When you convert STR_TO_DATE(date, '%d/%m/%Y') you will get a date with hours, minutes and seconds as 00:00:00

Using CURRENT_DATE perhaps will match more closely the original requirements

SELECT COUNT(*) as n 
FROM tracker
WHERE STR_TO_DATE(date, '%d/%m/%Y') <= CURRENT_DATE

Also I suggest you to rename the column 'date'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You do not need TIMESTAMP():

SELECT COUNT(*) as n
FROM tracker
WHERE STR_TO_DATE(date, '%d/%m/%Y') <= NOW()

Upvotes: 1

Related Questions