Solved

PHP Date Question

Posted on 2014-07-21
11
469 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can't Find the Homepage on my site 4 47
SQL Function 4 38
Wordpress contact form sending spam 2 53
Looking FTP apps that behaves similar to WinDirStat 3 35
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

751 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