Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP Date Question

Posted on 2014-07-21
11
Medium Priority
?
481 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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 668 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 84

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 664 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 668 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 111

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

660 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