How to convert user entered date to MySQL date to compare ...

[bsamson]

Hello ... I currently have a date field in my MySQL DB and ofcourse that's stored in this format:

2008-01-19 12:32:50

 

The format the end user enters the date is:

01/19/2008 which is stored in $reqdate

(i.e.: mm/dd/yyyy)

 

I currently have this MySQL query ...

<?php $query = mysql_query("SELECT * FROM nightly WHERE entrydate='$reqdate'");  ?>

 

Obviously this doesn't work. How do I rewrite the above query to check the end user date against the mysql formated date?

 

Any help will be very much appreciated! Thanks!

 

 

 

 

[revraz]

May be an easier way, but you could explode it on "/" then just take the 3 values and put them in any order you want.

 

 

[$username]

I dont use the Data/time format for MYSQL. It is easier to have the field a varchar and then just setup your PHP code to enter the date that you would like.

 

you can try doing a ereg_replace("[/]", "-") on your 01/19/2008 then just add the PHP time to make it work

 

code example $time = ereg_replace("[/]", "-",  $reqdate);

$mysql-time = $time, and your HH:MM:SS

 

 

Brett

 

 

 

 

[bsamson]

Thanks for everyones help. I just wish there was an easier way ... like a built in function to check only the date. Oh well, thanks everyone!

[revraz]

The problem is you are allowing a date format that you can't do much with.

 

So what you should do is allow them to use that date format for entering it, but you convert it after and store it correctly in the DB.  There is no function, unless you make one, that does this, because it doesnt know if you use

 

dd/mm/yy

mm/dd/yy

 

etc

[bsamson]

Ok one more question. Let's say I make the user (via a form) enter the date the way MYSQL datetime field.

 

So let's say I want to query the DB to match the date 2008/01/19 ... how to I write that to just search for the date? So basically disregard the entered time?

 

Thanks.

[bsamson]

For Example ... I tried this but it doesn't seem to work:

 

<?php 
$reqdate = "2008/01/19";
$query = mysql_query("SELECT * FROM nightly WHERE date('Y/m/d',strtotime(entrydate)=$reqdate");
?>

 

Any Suggestions?

[PFMaBiSmAd]

Use the mysql STR_TO_DATE() fucntion in your query to convert any format data into a mysql DATE type - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

[bsamson]

Actually I am trying this:

 

<?php
$query = mysql_query("SELECT * FROM nightly WHERE DATE_FORMAT(entrydate, '%c/%d/%Y')=$reqdate"); 
?>

 

Now, just trying to get it working

[PFMaBiSmAd]

Use the mysql DATE() function to only use the date part of your DATETIME type in a comparison - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date

 

Your query in your last previous post won't work because php code does not execute inside of strings.

[bsamson]

I am a little confused. Are you suggesting that I do this:

 

<?php
$query = mysql_query("SELECT * FROM nightly WHERE DATE(entrydate)=$reqdate"); 
?>

[bsamson]

Unfortunetly the above Query doesn't work. Any suggestions?