473,221 Members | 1,759 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,221 software developers and data experts.

Comparing Dates

I have a MySQL table with the date stored in three fields as string
values like this. (sorry, its imported data)

str_yy str_dd str_mm
------------------------
05 01 04
05 02 04

and so on.

I have a user input "from" and "to" date fields that are also string
values ("04/01/05" and "04/20/05")

I need to return only the records that are between or equal to the two
dates.

Being new to MySQL and PHP I am not sure if I should do this with code
or with query. Can someone help me get started?

Jul 17 '05 #1
3 3163
You may want to store your dates as timestamps using the Bigint or Int
column types. You can then perform numerical comparisons on them with great
ease. When you have three columns like that, with string values, you will
have to convert them to timestamps first (you can use the mktime()
function - http://us3.php.net/mktime) - but it's generally a headache. In
addition, I recommend that you use integer (or floating point) timestamps as
your table's primary key, so that you will also have a record of when each
entry was made.

PMK Media
http://www.pmkmedia.com
Jul 17 '05 #2
Here is what I came up with. Works pretty well. Any comments or
improvements welcome.

$dfrom = DATE($_POST['from']);
$dto = DATE($_POST['to']);
$query = "select * from database where date(concat(str_yy, '-', str_mm,
'-', str_dd)) >= '$dfrom' and date(concat(str_yy, '-', str_mm, '-',
str_dd)) <= '$dto'

Jul 17 '05 #3
Q: How can I compare two dates?
A:
You may compare dates just like any strings, but only in ISO 8601
format (ie, yyyy-mm-dd). Note, the delimiter (-) can be anything.
(e.g.) '2004-01-01' > '2003-12-31'

Refer:
http://www.iso.org/iso/en/prods-serv...esandtime.html

Jul 17 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: F | last post by:
Hi I have posted the question few days back about problem in inserting the dates in SQL server and thankful to them who replied. That was solved and this is a nice solution....
2
by: Mike N. | last post by:
I am currently using a function to validate a form on the client side (see code below). At the end of the function, I would like it to also compare a startDate against an endDate to ensure that the...
2
by: Duppypog | last post by:
I'm trying to compare a date stored in a database with today's date using an If statement, but it's not returning true. Example, value in database is 11/5/2003 with today being 11/6/2003. Can...
2
by: Philip Townsend | last post by:
I am having difficulty with a simple routine as follows: public static bool VerifyExpirationDate(DateTime date) { if(date>=DateTime.Now)return true; else return false; } The problem is that...
2
by: Manny Chohan | last post by:
Hi, i have two datetime values in format 11/22/04 9:00 AM and 11/22/04 9:30 AM. How can i compare dates .net c# or if there is any other way such as Javascript. Thanks Manny
2
by: ameshkin | last post by:
Hi GUys, Im trying to compare two dates in MYSQL. But its not treating the dates as numbers, but as strings. I try using strtotime but that did not work. Basically, if the last comment is...
12
by: colincolehour | last post by:
I am new to Python and am working on my first program. I am trying to compare a date I found on a website to todays date. The problem I have is the website only shows 3 letter month name and the...
4
by: cheryl | last post by:
I am using the PHP.MYSQL and Apache server application in developing my website. I have problem in comparing dates. Website has room reservation, the user will check first the room availability. The...
2
by: dantebothermy | last post by:
Is there a simple way to subtract the time from a datetime field, so when I compare dates, I'm always comparing the dates at 12:00 am? I'd really prefer not to convert all my dates to strings. ...
4
by: jupi13 | last post by:
i have this code..i don't know what where is the error in this one..it says data type mismatch..... Dim Mydate As Date Dim MydateString As String MydateString = "Text1.Text" Mydate =...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
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...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
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: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
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"....

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.