PHP Date Question

I have some php code were a user enters a date with datepicker...it gets entered into the phpMyAdmin SQL table in the format 07/04/2014. In the database the TYPE is not set as DATE, it is set as varchar(255). My question is, will I still be able to run a SQL to get a report that shows data entered between dates? Or does the type have to be set to DATE instead of varchar(255)?
LVL 15
wantabe2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
No, you won't be able to run that query and the date format you are showing won't work either.  You need to convert it to 2014-07-04 (yyyy-mm-dd) to put it into a MySQL datetime field.  Then you can run queries based on datetime arithmetic.
0
Dave BaldwinFixer of ProblemsCommented:
0
wantabe2Author Commented:
So, as long as the date format in the table is YYYY-MM-DD I should be able to run queries on it?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Randy PooleCommented:
If you  cast the varchar field then you can such as
Select * from myTable where Cast(myvarchardate AS DateTime) Between '2014-06-01' and '2014-06-15'

Open in new window

0
wantabe2Author Commented:
Can I edit this code so no matter how the date is typed in on the screen such as 7/4/2014 it wil be entered in the DB as 2014-07-04?


<head>
  <meta charset="utf-8" />

  <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
  <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
  <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
  <script>
  $(function() {
    $( "#datepicker_comp" ).datepicker();
    $( "#format" ).change(function() {
      $( "#datepicker_comp" ).datepicker( "option", "dateFormat", $( this ).val() );
    });
  });
  </script>
</head>

Open in new window

0
COBOLdinosaurCommented:
If it is entered as  7/4/2014 how are you suppoed to know if that means july 4, 2014 or apr 7, 2014.

You have to impose limits and force at least some standard input, then if you are going to use an alternate format you can parse and reformat.  Or you do like virtually every profissional developer does and limit the format at to point of entry so it always comes in the way you will store it and eliminate extra steps.

Cd&
0
Ray PaseurCommented:
Here are some of the basics.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

In PHP you can use strtotime() to convert the date into a timestamp and date('c') to recover the ISO-8601 format date.  Then you can insert the correctly formatted ISO-8601 date into the data base.
0
wantabe2Author Commented:
I just need it to be entered into the DB in the format YYYY-MM-DD...I guess I will just manually have the enter it into the field as such...
0
NerdsOfTechTechnology ScientistCommented:
Since the date is being entered in a KNOWN format (mm/dd/yyyy format I'm guessing) you can:

A) use a STR_TO_DATE() workaround each SQL query (not recommended)

B) create a new column in your table called temp_date with DATETIME and run a processing query to convert your VARCHAR date column result into the temp_date column, verify correctness, drop the VARCHAR date column, and rename your temp_date column to the original date column name.

TEST SELECT
SELECT STR_TO_DATE(`original_date`, `%m/%d/%Y`) as temp_date FROM yourTable

Open in new window

To populate the new DATETIME column:
UPDATE myTable
SET temp_date = STR_TO_DATE(original_date, `%m/%d/%Y`)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Randy PooleCommented:
Your best bet is to determine the locale settings in php and assume the user entered the date based upon that and the reformat it based upon how you want to store it in the database.
http://php.net/manual/en/function.strftime.php
0
Ray PaseurCommented:
Agree with NerdsOfTech -- that's a good solution.  Also agree with Randy Poole. If there is ambiguity in the date, such as CobolDinosaur showed us with 7/4/2014, knowing the locale of the client may be very helpful
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.