Solved

PHP Date Question

Posted on 2014-07-21
11
474 Views
Last Modified: 2014-07-22
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)?
0
Comment
Question by:wantabe2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 167 total points
ID: 40209999
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40210001
0
 
LVL 15

Author Comment

by:wantabe2
ID: 40210007
So, as long as the date format in the table is YYYY-MM-DD I should be able to run queries on it?
0
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
LVL 21

Expert Comment

by:Randy Poole
ID: 40210012
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
 
LVL 15

Author Comment

by:wantabe2
ID: 40210024
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
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 40210050
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
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 total points
ID: 40210183
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
 
LVL 15

Author Comment

by:wantabe2
ID: 40210602
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
 
LVL 19

Accepted Solution

by:
NerdsOfTech earned 167 total points
ID: 40210882
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211367
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40211545
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

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question