Convert mssql datetime object to PHP string

Question

I'm grabbing some information from a database and the record is in an MSSQL DateTime format, when I return it, it shows in my array as follows:

[arrayItem] => DateTime Object
    (
        [date] => 2008-06-05 09:14:11
        [timezone_type] => 3
        [timezone] => Europe/London
    )

When I try to extract this as an array (ie $array[arrayItem][date]) I get an error:

Fatal error: Cannot use object of type DateTime as array

I have also tried formatting the data in SQL before it is passed to the PHP, and the strtotime functions and had no joy.

Any recommendations would be very welcome, I'm tearing my hair out with this one!

Thanks

Giacomo1968 · Accepted Answer · 2019-02-01 00:17:18Z

This has caught me out a couple of times too.

Hopefully this will help you as it has myself :)

http://af-design.com/blog/2010/03/13/microsoft-sql-server-driver-for-php-returns-datetime-object/

Here's the gist of what that page is saying, in case the link goes down.

When querying against a column defined as a datetime, the native PHP SQL Server extension returns a string where as the Microsoft extension returns a DateTime object. So if you are expecting a string, you’ll need to adjust your code accordingly.

It goes on to explain that you can simply add an additional parameter to your requests to the database, specifying that you want your dates to be returned as strings. Simply add the ReturnDatesAsStrings parameter, specifying true.

Example:

$connectionParams = array(
    'USR'=>'user',
    'PASS'=>'pass',
    'Database'='myDatabase',
    'ReturnDatesAsStrings'=>true  //<-- This is the important line
);
$conn = sqlsrv_connect('127.0.0.1',$connectionParams);

Then you can use $conn as you would regularly for your sqlsrv database connection, only dates will return as strings, instead of DateTime objects.

Alternately, if all you wanted was a timestamp out of the datetime you could call:

$myDateTimeObject->getTimestamp();

Shakti Singh · Accepted Answer · 2011-07-12 10:31:54Z

6

You should access them like below. the associative index arrayItem contains an object which have some properties.

$array['arrayItem']->date;
$array['arrayItem']->timezone_type;

answered Jul 12, 2011 at 10:31

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

1 Comment

Nick Over a year ago

Thanks, but this just returns a null :( $array['arrayItem']->date;

Giacomo1968 · Accepted Answer · 2019-02-01 00:19:13Z

4

Try this. It works for me:

$array['arrayItem']->format('Y-m-d H:i:s');

Reference: http://php.net/manual/en/datetime.format.php

edited Feb 1, 2019 at 0:19

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered Mar 12, 2015 at 7:17

ron

84612 silver badges19 bronze badges

1 Comment

buycanna.io Over a year ago

Works great for me as well!

Giacomo1968 · Accepted Answer · 2019-02-01 00:19:28Z

2

Since its an object, you can't get the properties like you do with an array. You need the -> sign for accessing the date. In your question it seems as if you var_dumped the variable $arrayItem, so, use it like this:

$date = strtotime($array['arrayItem']->date]);

edited Feb 1, 2019 at 0:19

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered Jul 12, 2011 at 10:25

Sascha Galley

16.2k5 gold badges39 silver badges52 bronze badges

3 Comments

Nick Over a year ago

Thanks, tried this but it doesn't work unfortunately strtotime($array[arrayItem->date])

Sascha Galley Over a year ago

try strtotime($array['arrayItem']->date])

Nick Over a year ago

Many thanks, just returns a blank though - think something is screwed up my end! :) Brian's solution worked well for me though. Thanks again

Baz · Accepted Answer · 2012-10-11 10:22:36Z

1

echo $array['arrayItem']->format('Y-m-d H:i:s');

edited Oct 11, 2012 at 10:22

Baz

37k11 gold badges75 silver badges101 bronze badges

answered Jul 27, 2012 at 10:35

osnosn

111 bronze badge

Comments

Linesofcode · Accepted Answer · 2014-06-04 14:59:12Z

0

Another solution is to loop.

$_date = \DateTime::createFromFormat('D M d Y H:i:s e+', $the_date);
foreach($_dateStart as $row){
   echo $row; // returns 2014-06-04 15:00
   break;     // stops at the first position
}

answered Jun 4, 2014 at 14:59

Linesofcode

6,02114 gold badges72 silver badges132 bronze badges

Comments

Rob · Accepted Answer · 2017-03-24 02:40:45Z

0

This worked for me

date('Y-m-d', strtotime($rs->Fields('time')->value)

For Adodb connection on MSSQL Server

edited Mar 24, 2017 at 2:40

Rob♦

27.5k16 gold badges89 silver badges103 bronze badges

answered Mar 24, 2017 at 2:04

Sanjay

1,7851 gold badge20 silver badges29 bronze badges

Comments

Giacomo1968 · Accepted Answer · 2019-02-01 00:18:03Z

0

It is functionality and simple to use.

You can convert date from date object instead of string here is the way how to use:

$obj->arrayItem->format('H:i:s')
$obj->arrayItem->format('Y-m-d')

edited Feb 1, 2019 at 0:18

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered Jun 1, 2016 at 6:32

Chintan Gor

1,0722 gold badges15 silver badges38 bronze badges

Comments

Giacomo1968 · Accepted Answer · 2019-02-01 00:18:53Z

0

If you want to show data in PHP page you just copy and paste in <?php ?> code :

$serverName = "your_sqlserver";
$username = "your_username";
$password = "your_password";
$database = "your_database";
$connectionInfo = array( "UID"=>$username, "PWD"=>$password, "Database"=>$database, "ReturnDatesAsStrings"=>true);
$connection = sqlsrv_connect($serverName, $connectionInfo);

$result = sqlsrv_query( $connection,"SELECT  * from table'");
$msrow = sqlsrv_fetch_array($result);

print_r($msrow['column_name']);

edited Feb 1, 2019 at 0:18

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

answered May 9, 2016 at 8:59

Mahabubur Rahman masum

153 bronze badges

Comments

Jan Turoň · Accepted Answer · 2021-04-07 09:10:26Z

0

When you create the query like

SELECT created FROM yourTable ...

and created is DATETIME type, the result is the object as you described. To return the date part as a string, you can solve it on the SQL level as

SELECT CONVERT(varchar, created, 23) AS created FROM yourTable ...

see the 23 meaning here.

answered Apr 7, 2021 at 9:10

Jan Turoň

33.1k24 gold badges139 silver badges179 bronze badges

Collectives™ on Stack Overflow

Convert mssql datetime object to PHP string

10 Answers 10

Comments

1 Comment

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

Comments

1 Comment

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related