471,108 Members | 1,296 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

mySQL select datetime less than or equal to - problem!

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!!!
Jul 20 '05 #1
1 82296
Propel Exacto wrote:
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!


'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_datetime) BETWEEN '2004-10-01' AND '2004-10-03';

Regards,
Bill K.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by hokieghal99 | last post: by
6 posts views Thread by Hayden Kirk | last post: by
23 posts views Thread by phpfrizzle | last post: by
7 posts views Thread by mike-nospam | last post: by
1 post views Thread by Nick | last post: by
reply views Thread by Andreas Paasch | last post: by
9 posts views Thread by jossinet | last post: by
1 post views Thread by Mike the Canadian | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.