Mysql Date Format dd-mm-yyyy

[lexer]

Hi

I have Mysql table with the following fields Ext, date, time, The field date is defined as VARCHAR and I store the date in the following format: dd-mm-yyyy, I get reports from mysql in a date range, For Example, between 01-06-2009 to 05-06-2009, My problem is that the query also gets data from another months, example: 01-05-2009, 02-05-2009 etc..

This is the my query:

$query2 = "SELECT Ext, date, time ".
"FROM registers ".
"WHERE date BETWEEN '01-06-2009' AND '05-06-2009' ORDER BY date ";
$result2 = mysql_query($query2) or die(mysql_error());


How Can I do for getting just the data between range?
Do I have to use the Mysql DATE_FORMAT function?.
Do I have to convert date field to the variable type "date" in mysql?.

Thanks!!

 

[r937]

replace your two date and time columns with one single DATETIME column

once that's done, try your query again, and remember, when you give date literals to mysql, they ~must~ be in year-month-day sequence

so change this --

BETWEEN '01-06-2009' AND '05-06-2009'

to this --

BETWEEN '2009-06-01' AND '2009-06-05'





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon