473,233 Members | 1,438 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,233 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 83848
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...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
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...

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.