wantabe2
asked on
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)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lots more info: http://us3.php.net/manual/en/book.datetime.php
ASKER
So, as long as the date format in the table is YYYY-MM-DD I should be able to run queries on it?
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'
ASKER
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>
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&
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&
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/f unction.st rftime.php
http://php.net/manual/en/f
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