Monday, November 15, 2010

Get date only from ODBC datetime in PHP

I always have problem with retrieving datetime field from ODBC. PHP will default to display it as "yyyy-mm-dd hh:mm:ss" format. The data is also converted to text. That means that you would not be able to do date calculation with it.

Normally I would use strtotime() to convert the date to timestamp first then perform the date manipulation. There are times when the date part of the datetime field is required. In which case I will have to include in the sql "convert(varchar,mydatefield,120)". Problem is MSSQL format 120 is "yyyy-mm-dd hh:mm:ss". There is no format that retrieves only "yyyy-mm-dd".

Fortunately the "convert" function allows truncation. So instead of using "convert(varchar,mydatefield,120)". I would use "convert(varchar(10),mydatefield,120)". The output will be a nice date only result.

If you do $date1-$date2, you will probably get a result like 1814400 because the returned result is in milliseconds. you need to divide the result by 60*60*24 to get days only result.

No comments:

Post a Comment