select date range

How to select between date to date?

[Jaunty Edward]

Hi,

I have to run a query in mysql where records from a date range are selected. I need to know how to make the query of following type:

select * from datetest
where date between '11-11-2001' AND '01-10-2009'
order by date desc

It will be great if someone can tell me which is the best format for dates:

d/m/y
d-m-y
dmy
12-june-2001
12-06-2001
12-06-01

I am so confused,
Thanks for help

Jaunty

[dreamcatcher]

Store your data in a DATE field. The format for this is Y-m-d:

Then use:

select * from datetest
where date between '2001-11-11' AND '2009-10-01'
order by date desc

dc

[Jaunty Edward]

Hi thanks for the help,
one more question: is there nothing that can be done if the date format has been used as 11-June-2001

Thanks Again

[mcibor]

That's just text (string) and you cannot compare string with another, not with > nor <.

What you can do however is to change the format with php:

$months = array("January" => "01", "February" => "02", ... "December" => "12");
$sql = "ALTER TABLE datetest ADD data DATE AFTER date;
mysql_query($sql);

$sql = "SELECT id, date FROM datetest";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result))
{
$fields = explode("-", $row['date']);
$month = fields[1];
$data = $fields[2] . "-" . $months[$month] . "-" . $fields[0];

$sql = "UPDATE datetest SET data='$data' WHERE id='". $row['id']."'";
mysql_query($sql);
}
$sql = "ALTER TABLE datetest DROP COLUMN date";
mysql_query($sql);

$sql = "ALTER TABLE datetest CHANGE data date DATE";
mysql_query($sql);

However if I were you I wouldn't use date for a field name, as it's a reserved word.
Best regards
Michal Cibor

[Jaunty Edward]

Hi,

thank you so much for the script and the tip.

Bye

[bunkermaster]

or use UNIX_TIMESTAMP() in your SQL query and sort with timestamps.