Weird dates from mssql (4.2.3 / 4.4.0-3)

[davemateer]

Hi

SQL Server 2000 Database:

Table: Thing
Columns: id (int), nameOfThing (int), dateOfThing (datetime)

I put some data in it:

1, dave, 9/11/1973 14:33:22
2, bill, 12/6/1980 13:23:33

Then I run a query from PHP4.4.0-3 (on Ubuntu) from Apache2.

<?php
$db_server = "192.168.1.123";
$model_connection = mssql_connect ($db_server, "user", "password");
mssql_select_db ("davetest", $model_connection);
$sql = "select dateOfThing from Thing where id = 1";
$result = mssql_query($sql, $model_connection);
$myarray = mssql_fetch_row ($result);
print_r($myarray);
?>

Result is this:

Array ( [0] => Tue Sep 11 00:00:00 1973 )

When I really want: 9/11/1973 14:33:22

I thought I'd fixxed it with the php.ini line:

mssql.datetimeconvert = Off

However it still returns the same 'converted' datetime.

I have restarted Apache and restarted the machine. I have made sure
this is the correct php.ini file I'm editing too.

Summary

On our production box which is running PHP4.2.3 I get:

PHP4.2.3 - Sep 11 1973 2:33PM (notice it is even chopping off seconds)

PHP4.4.0-3 - Tue Sep 11 14:33:22 1973

How to turn off this date conversion / formatting?

Cheers

Dave.

[davemateer]

More info:

sajuks on experts-exchange.com kindly suggested:

echo date("m/d/Y h:i:s", strtotime($row['dateOfThing']))."<br>";

which wil work.. however we've got a lot of legacy code which will need
fixxing (groan).. so I'd rather try and get the dates coming from the
database/driver in the old way.

Cheers.

Dave.

[Mladen Gogala]

On Thu, 16 Feb 2006 13:24:30 -0800, davemateer wrote:

How to turn off this date conversion / formatting?

Get Oracle.

--
http://www.mgogala.com

[crucialmoment]

>From the manual...
The DATETIME type is used when you need values that contain both date
and time information. MySQL retrieves and displays DATETIME values in
'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01
00:00:00' to '9999-12-31 23:59:59'.

From the result, it appears you are not entering the correct format.

Instead of 9/11/1973 14:33:22
enter '1973-09-11 14:33:22'

You can format your date within your qry like this
SELECT DATE_FORMAT(dateOfThing , '%Y - %m %d ') ...;
-> '2006-01-21'

Or use php to format the result
$formatted_date = date("Y-m-d",strtotime($myarray['dateOfThing']));
-> '2006-01-21'

http://dev.mysql.com/doc/refman/5.0/...functions.html
http://us3.php.net/manual/en/function.date.php

[davemateer]

Thanks for the replies (getting Oracle would be nice...however MSSQL is
really quite good).

On that note cruicalmoment, I'm doing everying in Microsoft SQL Server
2000, and not MySQL... but thanks for the reply.

All the best

Dave.