Solved

PHP Date Question

Posted on 2014-07-21
11
466 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …

735 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