Timestamping different in MSSQL...?

[woolyg]

Hi all,

 

I am using PHP to get a datetime value out of an SQL DB. The field type is datetime, and the fomat it is stored in is  2007-05-21 02:56:59.000, or YYYY-MM-DD HH:MM:SS.xxx

 

When I use the following query in PHP to grab the date info

$get_events = "
SELECT 
e.OccurrenceTime
FROM 
dbo.ExTable e
";
$run_events = mssql_query($get_events);
while($display_events = mssql_fetch_assoc($run_events)){ // WHILE 52

$OccurrenceTime = $display_events['OccurrenceTime'];

echo $OccurrenceTime."<br />";

} // WHILE 52

 

.. it returns timestamps with the layout May 21 2007 2:56AM.

 

I'd like it to be of the format 2007-05-21:02:56:59

 

Can anyone point out how I can get this done? It outputs the datetime format just fine with mysql 

 

All help appreciated,

WoolyG

[cags]

Seems odd that it's working that way, but since I've never worked with MSSQL that's perhaps normal. I would expect MSSQL to support some kind of formatting syntax for the date. But since I'm not familiar with it a Google search will be your best bet. Alternatively you could convert it with PHP...

 

$OccurrenceTime = date("Y-m-d H:i:s", strtotime($display_events['OccurrenceTime']));

[woolyg]

Found out what I needed to do -

 

$get_events = "
SELECT 
CONVERT(VARCHAR(19), e.OccurrenceTime, 120) as event_timestamp 
FROM dbo.ExTable e
";

 

This outputs the timestamp in a php-DATETIME-friendly format!

 

Hope this helps someone else too - solved:)

 

WoolyG