473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 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 83908
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: hokieghal99 | last post by:
Hi, I'd like to get user input from an html form into a mysql select statement. Here's where I'm stumped: $result = mysql_query("SELECT * FROM dept WHERE notes LIKE '%search-string%'",$db); ...
6
by: Hayden Kirk | last post by:
Hi Currently im using this $query = "select * from news, users where news.User_ID = users.User_ID order by news.News_ID DESC" only problem is both tables have User_ID, how can I make...
23
by: phpfrizzle | last post by:
Hi there, I have a site with products on it. The site has a mysql backend. All products belong to certain series (table series). There can be up to 4 different products (table products)...
7
by: mike-nospam | last post by:
Trying to write a php script where the user will enter the row number (index) of a record. Say, for example, user wants record 55: : select * from MyTable where Index = "55" where Index is an...
1
by: Nick | last post by:
hi, all I convert some code from access to mysql. And I have a InboxMessage table which has From and To field. So, the query is like: select * from InboxMessage where To=12 12 is user id. This...
0
by: Andreas Paasch | last post by:
I'm having a little problem here that seems difficult to solve - to me. I'm working on several tables at one time and once in a while I need to update them based on a previous select statement....
9
by: jossinet | last post by:
Bonjour, j'ai une table : matable dans laquelle j'ai un champs : codepostal Ce champs code postal est rempli d'enregistrements qui ont des valeurs de 5 chiffres. Je souhaite créer un bouton...
1
by: Mike the Canadian | last post by:
Suppose I have a MySQL 5 table with a DateTime field in it. The table has two records where the time is blank. Running: SELECT CAST(DateTimeField AS TIME) FROM MyTable shows two records with...
2
Spazasaurus
by: Spazasaurus | last post by:
I am having trouble. I am not sure if it is not possible or not, but don't know any alternatives. I am converting my site from PHP and MYSQL to ASP.NET and MSSQL. In my current site. I did a query...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.