PHP / SQL - Datetime format messed up PHP Side

Question

I'm storing a datetime timestamp in the database (SQL Server). I simply use this to get the current time: date('Y-m-d H:i:s') which works fine.

In the database it looks exactly the same (the format), however, when I get the datetime from the database the format is completely off.

The date is for example displayed in the database as 19-4-2016 13:29:36, but when I read the data in PHP it becomes apr 19 2016 01:29:36:000.

My code for reading it from the database:

$dateFromDB = /* Sql query here.. */;
$myTimestamp = date('Y-m-d H:i:s', strtotime($dateFromDB));

The $dateFromDB variable echos: apr 19 2016 01:29:36:000

The $myTimestampechos: 1970-01-01 01:00:00

The date is stored in the database as datetime, be aware that the date format looks good inside the database table, it's messed up when it is being read at the PHP side.

It looks like this in the database:

How do you fetch it from the database? It should be "YYYY-MM-DD HH:MM:SS" according to the documentation: "the format for these values is 'YYYY-MM-DD HH:MM:SS[.fraction]'". Are you certain it's stored as a DATETIME or is it actually a VARCHAR? Regardless, you can use DateTime::createFromFormat(). Also, are you running MySQL or MsSQL? You've tagged it with MySQL but "Microsoft SQL Server" is not MySQL. — h2ooooooo
– h2ooooooo, Commented Apr 19, 2016 at 11:45
if you are storing correctly then why you are using strtotime and date function. Simple echo $dateFromDB. I don't think you need to convert value — urfusion
– urfusion, Commented Apr 19, 2016 at 11:46

guri · Accepted Answer · 2016-04-19 11:51:33Z

2

From Question :The date is for example displayed in the database as 19-4-2016 13:29:36

19-4-2016 13:29:36 should be 2016-04-19 13:29:36 (Y-m-d H:i:s) format in Database.

answered Apr 19, 2016 at 11:51

guri

6842 gold badges8 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2024006 · Accepted Answer · 2016-04-19 12:17:05Z

0

@Dubb :- I used the same date to store in the database and then tried to retrive it. I am able to retrive in the same format as I had stored earlier. I used this code to fetch from database :-

if ($result->num_rows > 0) 
{
   // output data of each row
   while($row = $result->fetch_assoc()) 
   {
     $ff =  $row["dates"];
     echo date('Y-m-d H:i:s', strtotime($ff)) . "<br>";
   }
} 
else 
{
   echo "0 results";
}

If possible please describe your table column format

answered Apr 19, 2016 at 12:17

user2024006

2 Comments

Dubb Over a year ago

Hey, it's just 'datetime'. When I click the column i'm able to select from a calender, so SQL server reads it as a proper date aswell.

user2024006 Over a year ago

Have you set proper timezone in php?

Collectives™ on Stack Overflow

PHP / SQL - Datetime format messed up PHP Side

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related