MSSQL Date Conversions again!

markfc

Hi,

I have a basic MS SQL 2000 database that has a datetime field.

Now if i use sql query analyser or enterprise manager it returns the date as ;

2003-03-31 14:17:22.000

which would be ok for what i want.

However when i run and SQL Query from PHP that is the same command

I get the date back as

31 Mar 2003 14:17

which obviously isn't much good to me.

Can anyone tell me how to get php to retrieve this as ;

dd/mm/yyyy hh:mm:ss

Thanks

Mark

kevinsequeira

post the code that u use to retrieve the date.

also take a look at the Date() function

http://ca.php.net/manual/en/function.date.php

reg
kevin

markfc

i basically do

$sql = "SELECT ww_news_expires, from tablename" ;

i'm not trying anything clever!

Thanks

kevinsequeira

if ww_news_expires is datetime then it is stored as yyyy-mm-dd hh:mm:ss

$sql = "SELECT ww_news_expires from tablename" ;
$result = mysql_query($query);
$row = mysql_fetch_array($result);
print (date("d/m/Y h:i:s",strtotime($row['ww_news_expires'])));

EDIT : didnt realize it was MS SQL, the functions to get the data from db are for MySQL but the date function should work. what was required was to convert the data that you retrieved from string to time and then run the date function on it
reg
kevin

EricC

Just a suggestion. I've use the CAST function of T-SQL for datatime columns and would do something like this:

$sql = "SELECT CAST(ww_news_expires AS TEXT), from tablename" ;

Makes taking that data and manipulating the string easier depending whatelse you're doing with it.

Good luck. 🙂

markfc

i tried cast as text, but thats an invalid conversion? i guess cause of the \ in it?

but i tried cast to varchar and i get

Dec 5 2003 2:12PM

instead....

aaargh this is doing my head in !