Comparing a string-date with another string-date in MySql query

Question

I'm trying to get entries from MySql server where the dates are > date_begin and < date_end

My problem is that the table column "date" is set to VARCHAR, and the string I am comparing it is also directly from an <input type="text"/>

The date format ( as string / varchar ) in the mysql table and in the textbox will be DD.MM.YYYY

my problem is that if i do

( php code )

 $query = "SELECT * FROM table WHERE date>='" . $dateBegin . "' AND date<='" . $dateEnd . "'";

the result will be completely random.

I know why this happening so the question is: How can I compare this to get the right result ?

thank you all who can help

UPDATE :

Now I've tryed this solution, but it also doesn't work.

$query = "SELECT * FROM artikel WHERE STR_TO_DATE(datum, '%d.%m.%Y')>=STR_TO_DATE('" . $_REQUEST['dateTo'] . "', '%d.%m.%Y')" . "' AND STR_TO_DATE(datum, '%d.%m.%Y')<=STR_TO_DATE('" . $_REQUEST['dateTo'] . "', '%d.%m.%Y')";

the $_REQUEST['dateFrom'] and $_REQUEST['dateTo'] came from 2 textboxes and the input is a string like this "05.06.2005".

in the db-table 'artikel' (german for articles) in the column 'datum' (german for date) the values are VARCHAR's with the same format ( "05.06.2005" ).

Keeping a date as a string, and then processing it as a date, means that you create additional coding overheads, additional load on the server, use additional disc space on the server, introduce the possibility of 'dates' with incorrect formatting, and destroy the ability of the SQL Engine to use indexes to search that field. [I can't over-emphasise this too strongly]: Store data in the correct data-type, don't store strings and convert them 'on the fly'; you're headed for a world of hurt, inefficiencies, and debugging hell. — MatBailie
– MatBailie, Commented Oct 30, 2012 at 13:13
it's not my mysql server, if i would have the ability of changing the datatype to "date" i proppably would. i'm just the guy who has to add a filter function to an existing web-application. but thanks anyway — Ace
– Ace, Commented Oct 30, 2012 at 13:21
The you have my sympathy, I'm all to used to the My-Hands-Are-Tied type of pain. May the Lord have mercy on your soul. — MatBailie
– MatBailie, Commented Oct 30, 2012 at 13:29

davek · Accepted Answer · 2012-10-30 13:18:12Z

9

Always compare dates with dates, integers with integers etc.

With MySQL you can use the str_to_date functions to specify how your string-date is to be processed.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

e.g. if your date column is called "mydate":-

SELECT * from table
where STR_TO_DATE(mydate, '%m/%d/%Y') >= STR_TO_DATE('04/31/2004', '%m/%d/%Y')
...

And always ensure you're validating any string input from the user to avoid SQL injection attacks.

EDIT: as Dems points out above, this is suboptimal as a date value should be stored as date value in the database.

edited Oct 30, 2012 at 13:18

answered Oct 30, 2012 at 12:50

davek

23k11 gold badges80 silver badges97 bronze badges

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

9 Comments

Ace Over a year ago

i'll try this. is this STR_TO_DATE(date, '%m/%d/%Y') a valid MYSQL function ?

davek Over a year ago

@Ace: yes. See the link I pasted in to the answer.

davek Over a year ago

@Ace: you would put your "$dateBegin" in place of the "4/31/2004" etc.

Ace Over a year ago

so the first attribute is the actual "string-date" and the second attribute is the format ? hab i use dots "." instead of "/" ?

Ace Over a year ago

ok thank you, is there another method to compare with Prices like 15,39 or 15.39 ? i would be glad if you could post that as edit in your answer.

|

SRIRAM · Accepted Answer · 2012-10-30 12:50:36Z

1

you can use between

"SELECT * FROM table WHERE date between'" . $dateBegin . " AND " . $dateEnd . "'";

answered Oct 30, 2012 at 12:50

SRIRAM

1,8982 gold badges17 silver badges18 bronze badges

1 Comment

Ace Over a year ago

the problem is that MYSQL can't compare dates as string.

Collectives™ on Stack Overflow

Comparing a string-date with another string-date in MySql query

2 Answers 2

9 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related