I have a text column in mysql and it stores a date value in the format yyyy-mm-dd. Now, in my php page, I use this code to parse into a date value.
date("F j, Y", strtotime($row['value']));
Now, I just read that strtotime() parses values only after January 1, 1970. I have lot of date values before that date. Is there a work around? I don't want to change my database structure.
From the documentation for strtotime():
strtotime() has a range limit between Fri, 13 Dec 1901 20:45:54 GMT and Tue, 19 Jan 2038 03:14:07 GMT; although prior to PHP 5.1.0 this range was limited from 01-01-1970 to 19-01-2038 on some operating systems (Windows).
What version of PHP are you running? And on what platform? Perhaps it's time for an upgrade.
If you're working with dates outside the 13 Dec 1901 to 19 Jan 2038 range, then consider using PHP's DateTime objects which can work with a much wider range of dates.
Procedural:
$date = date_create($row['value']);
if (!$date) {
$e = date_get_last_errors();
foreach ($e['errors'] as $error) {
echo "$error\n";
}
exit(1);
}
echo date_format($date, "F j, Y");
OOP:
try {
$date = new DateTime($row['value']);
} catch (Exception $e) {
echo $e->getMessage();
exit(1);
}
echo $date->format("F j, Y");
you should use a date columnn, not text one.
and date_format() SQL function to format date in the query
$date = DateTime::createFromFormat('d M Y','17 Jan 1900');
echo $date->format('Y-m-d');
Just to update this for modern times, PHP7 strtotime() on a 64 bit system can handle virtually any AD date, although for some reason the man page still says 1970.
$t = strtotime("4/1/1492");
echo "Stamp: $t\n";
echo date("m/d/Y",$t);
Result:
Stamp: -15076350000
04/01/1492
And
$t = strtotime("8/1/4921");
echo "Stamp: $t\n";
echo date("m/d/Y",$t);
Result
Stamp: 93142933200
08/01/4921
function safe_strtotime($string)
{
if(!preg_match("/\d{4}/", $string, $match)) return null; //year must be in YYYY form
$year = intval($match[0]);//converting the year to integer
if($year >= 1970) return date("Y-m-d", strtotime($string));//the year is after 1970 - no problems even for Windows
if(stristr(PHP_OS, "WIN") && !stristr(PHP_OS, "DARWIN")) //OS seems to be Windows, not Unix nor Mac
{
$diff = 1975 - $year;//calculating the difference between 1975 and the year
$new_year = $year + $diff;//year + diff = new_year will be for sure > 1970
$new_date = date("Y-m-d", strtotime(str_replace($year, $new_year, $string)));//replacing the year with the new_year, try strtotime, rendering the date
return str_replace($new_year, $year, $new_date);//returning the date with the correct year
}
return date("Y-m-d", strtotime($string));//do normal strtotime
}
I'm a noob, and was having some similar issues regarding the use of strtotime vs explode. I ran across this thread and was greatly helped by the comments on the change in date limit by Mark Baker (thanks!). So I wrote this code just to play with the concept. Perhaps it will help other noobs such as myself.
Just change the date on the top PHP line and see what happens to the dates below - very interesting. Thanks again!
<?php $dob = "1890-11-11"; ?>
<html>
<head>
<style>
.inputdiv {
width:200px;
margin:100px auto 10px auto;
background-color:#CCC2FC;
text-align:center;
border:1px solid transparent;}
.spacer{
width:199px;
margin:20px auto 20px auto;}
</style>
</head>
<body>
<div class="inputdiv">
<div class="spacer"><?php echo "Raw dob: ".$dob ?></div>
<div class="spacer"><?php echo "Strtotime dob: ".date("m-d-Y", strtotime($dob)) ?></div>
<div class="spacer"><?php list ($y, $m, $d) = explode('-', $dob);
$dob = sprintf("%02d-%02d-%04d", $m, $d, $y);
echo "Explode dob: ".$dob ?></div>
</div>
</body>
</html>
I have a Sql database dealing with when each state within the U.S. was founded, going back to 1787. This field is set DATE within the database, in the YYYY-MM-DD format (ex. 1787-12-07). I tried many methods but the most simple and effective I found was: (the exact clip of code I use. change the information & variables as you need.)
<?php
$page = 'geo-usa.php';
$sort = 'stateName';
if (isset($_GET['sortBy'])){
$sortBy = $_GET['sortBy'];
if($sortBy == 'stateEst'){
$sort = 'stateEst'; }
if($sortBy == 'capital'){
$sort = 'stateCapital'; }
if($sortBy == 'capitalEst'){
$sort = 'stateCapitalEst'; } }
$con=mysqli_connect($servername, $hostname, $password, $database);
if (mysqli_connect_errno()){ print "Failed to connect to MySQL: " .
mysqli_connect_error(); exit(); }
$stateData = "SELECT * FROM state ORDER BY $sort ASC, stateEst ASC";
$stateData2 = mysqli_query($con, $stateData);
while($stateData3 = mysqli_fetch_array($stateData2, MYSQLI_ASSOC)){
$stateID = $stateData3['stateID'];
$stateName = $stateData3['stateName'];
$stateAbr = $stateData3['stateAbr'];
$stateEstYear = $stateData3['stateEstYear'];
$stateEst = $stateData3['stateEst'];
$stateCapital = $stateData3['stateCapital'];
$stateCapitalEst = $stateData3['stateCapitalEst'];
$stateMotto = $stateData3['stateMotto'];
//Here is how you format a yyyy-mm--dd date within a database
$stateEstDate = date_create($stateData3['stateEst']);
$stateEstFormat = date_format($stateEstDate, "l F d, Y");
/// <- remove comments -
print "<table border='0' bordercolor='lime' cellpadding='1'
cellspacing='1' width='100%'>
<tr>
<td align='left' style='padding-top:10px;'>
<a href='$page' style='font-size:14px; color:red; text-
decoration:none; padding-left:7px'>
$stateName</a></td>
</tr>
<tr>
<td align='left' style='padding-left:20px;'>
<a href='$page?sortBy=stateEst' style='font-size:10px;
color:white; text-decoration:none;'>
$stateEstFormat </a></td>
</tr>
</table>"; } ?>
The output for the first 2 rows is:
Delaware Friday December 07, 1787
Pennsylvania Wednesday December 12, 1787
