We help IT Professionals succeed at work.

Confused about date formats between PHP and MySQL

alicia1234
alicia1234 asked
on
Medium Priority
214 Views
Last Modified: 2012-05-07
I've been wrestling with this most of the day and have finally decided to ask for help. I have a form with two dates, a "from" date and a "to" date.

I've got the form set up to accept dates in either mm/dd/yyyy or mm-dd-yyyy format. That's all working fine. So basically I have my dates as strings with the month first, then the day, then the year.

How do I now use these dates in a MySQL query? Using as is just doesn't work. Probably because MySQL wants to see the dates as yyyy-mm-dd, right? Do I actually have to convert them to that format? I did that and it does seem to work, but it also feels "klugey" and I wonder if there is some better way?

Also, after I converted the dates to yyyy-mm-dd strings to use in the where clause, I still have a problem because, for example, my "to" date is 2009-05-21, and there is a date/time in the database that is 2009-05-21 11:14. When I do a "less than or equal to" on the "to" date, it doesn't include this date, probably because what it's using is a time of 00:00.

What's the right way to be doing these date comparisons? All I want to do is have the user put in a start and end date, and then query the database to include all records between the two dates, inclusive. So 06/01/2009 to 06/30/2009 would give everything in June.

And on another note, just a simple quick question: do you use != for not equal, or !== ? It seems I've used both and both seem to work.

Thanks.
Comment
Watch Question

Commented:
Are your times in the table as unix timestamp?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
No they are "datetime".

I "kluged" the "to" date by adding " 23:59:59" to the end of it, and that now works correctly, but, again, it's not very "elegant". ;-)

Commented:
any other solutions generally require more code.

Author

Commented:
So my basic approach is ok, given that the dates are "datetime" in MySQL? My code is below. I didn't include the functions here but you'll get the gist. The code does work.

The query is:

$query =  "SELECT * FROM tblSurveys WHERE ( (surveydate >= '{$startdate}') && (surveydate <= '{$enddate}') ) ORDER BY surveydate DESC";  

if (	isset($_POST['startdate']) && $_POST['startdate'] != '') {
			if (isDate($_POST['startdate'])) {
				$startdate = datestring_to_MySQL($_POST['startdate']);
			} else {	
			$message = ERR_INVALID_DATE; 
			}
		} else {
			$startdate = '1950-01-01'; //a long time ago!
		}

Open in new window

Author

Commented:
oops - my code got cut off ... here it is again:

if (isset($_POST['startdate']) && $_POST['startdate'] != '') {
if (isDate($_POST['startdate'])) {
$startdate = datestring_to_MySQL($_POST['startdate']);
} else {	
$message = ERR_INVALID_DATE; 
}
} else {
$startdate = '1950-01-01'; //a long time ago!
}
 
if (isset($_POST['enddate']) && $_POST['enddate'] != '') {
if (isDate($_POST['enddate'])) {
$enddate = datestring_to_MySQL($_POST['enddate']);
$enddate .= ' 23:59:59'; 
} else {	
$message = ERR_INVALID_DATE; 
}
} else {
$enddate = '2099-12-31'; //a long time in the future
}

Open in new window

Author

Commented:
kevinu: While you were posting the 23:59:59 "kluge", I was doing just that. But good to hear that it's kind of the "accepted" way to do it.

Commented:
Well, its my accepted way.... there may be arguments to the contrary.

You could store using the date data type instead of datetime.

There are combinations of functions (either php string stuff or date manipulation in php) that would allow you to store only the date (truncate the time) before inserting the data in the first place.

There combinations of functions to pull only the date from a datetime field (involves converting to a string, and truncating the string)

This may provide more insight.

http://dev.mysql.com/doc/refman/5.0/en/datetime.html


Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.