mySQL select datetime less than or equal to - problem!

[Propel Exacto]

Hey guys,

I am using MySQL 4.0.18 and I have a field named "order_datetime " in
which I store data in the format 20041001 23:00:00 (for example Oct 1,
2004 11pm)

When I do a select statement to find dates in a given range, the
result set never includes records that have the ending date. For
example, if my SQL statement is

Select * from HH_Will_Call where (order_datetime >= (20041001) and
order_datetime <= (20041003)) order by order_datetime

It will only include records through Oct 2, even though I know there
are records in the table that have Oct 3!

As you can see, I am using the less than or equal to operator, yet I
have this problem with any date range. How can I get the ending date
to be included? HELP!!!

[Bill Karwin]

Re: mySQL select datetime less than or equal to - problem!

Propel Exacto wrote:[color=blue]
> Select * from HH_Will_Call where (order_datetime >= (20041001) and
> order_datetime <= (20041003)) order by order_datetime
>
> It will only include records through Oct 2, even though I know there
> are records in the table that have Oct 3![/color]

'20041003 23:00:00' is greater than '20041003', because '20041003' is
equivalent to '20041003 00:00:00'.

Recommendations :

1. Store dates as a DATETIME datatype, not a string datatype.

2. Use date & time manipulation functions.
See http://dev.mysql.com/doc/mysql/en/Da...functions.html

3. Extract the date portion of a DATETIME values when comparing days
without regard to the time portion:
SELECT * FROM HH_Will_Call
WHERE DATE(order_date time) BETWEEN '2004-10-01' AND '2004-10-03';

Regards,
Bill K.