Comparing Dates

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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

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?

Re: Comparing Dates

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 - ) - 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

Re: Comparing Dates

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'

[FAQ] How can I compare two dates?

Q: How can I compare two dates?
  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'


Site Timeline