CODEWITHSUNDEEP
mysqlsqldate
user3042803
user3042803

Reputation:

Date Comparison Not Working (Dates are strings) - MySQL

I have a MySQL query that reads:

select sum(AmtPd) as HCSA from HC062017 where CostCenter = '8718' and ServiceDate > '08/31/2013'

Have been using it for several months with no problem. Today I encountered a problem with it not working. ServiceDate is a var(10) containing a date pulled from a report. The format is mm-dd-yyyy. What appears to be happening is if the ServiceDate is in 2013 (which some of them still are), then the statement works perfectly. However, when the ServiceDate is in 2014, no records are selected. If I leave off the ServiceDate parameter, I get the desired results (since all records this time are past the 08/31/2013 date). Maybe I lucked out with the months '09', '10', etc, falling after '08'. I've tried using str_to_date with no luck 

select sum(AmtPd) as HCSA from HC062017 where CostCenter = '8718' and str_to_date(ServiceDate, '%m, %d, %Y') > '08-31-2013';.

Any suggestions on how to handle this?

Upvotes: 0

Views: 2947

Answers (2)

sashkello
sashkello

Reputation: 17871

You need to convert both to common format:

str_to_date(ServiceDate, '%m-%d-%Y') > str_to_date('08-31-2013', '%m-%d-%Y')

Comparing strings like you did is a bad idea. Strings are compared in alphabetic order, so '08-31-2013' is "smaller" than '09-30-2010' because 9 is compared to 8 before everything else. You can compare dates in ISO format YYYY-MM-DD.

Upvotes: 2

John Conde
John Conde

Reputation: 219864

STR_TO_DATE produces a date in a format of YYYY-MM-DD. So your date that you are comparing to must be in the same format:

str_to_date(ServiceDate, '%m, %d, %Y') > '2013-08-31';

Upvotes: 1

Related Questions