By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,925 Members | 1,831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,925 IT Pros & Developers. It's quick & easy.

Comparing Dates

P: n/a
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?

Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
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
Jul 17 '05 #2

P: n/a
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

P: n/a
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'


Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.