CODEWITHSUNDEEP
phpsqlsql-server
ytse_jam
ytse_jam

Reputation: 185

PHP/MS SQL Server display sql server datetime

im trying to display date and time from MSSQL Server datetime table using PHP, the value from from SQL Server is datetime EX. 2012-08-20 06:23:28:214. Now i want to display it the exact result but it php displays it like this EX. Aug 20 2012 6:23AM. I Have tried to use strtotime but the milliseconds does not display or rather display :000.

Any Help is so much appreciated. Thanks in Advance.

Upvotes: 2

Views: 1333

Answers (3)

John Woo
John Woo

Reputation: 263943

Try this,

SELECT CONVERT(VARCHAR(50), CAST('2012-08-20 06:23:28:214' as DateTime), 100) 
          AS [OutPut]

-- expected output
--     Aug 20 2012 6:23AM

from the example above, I used CAST in order to convert string date into the original DateTime datatype. But in the original query, you can omit the CAST function since you've mentioned that the column is already on the DateTime data type.

SELECT CONVERT(VARCHAR(50), columnDateTime, 100)

SQLFiddle Demo

How to Format Date and DateTime in MSSQL Server

Upvotes: 1

Fluffeh
Fluffeh

Reputation: 33542

The DateTime object has the input string type of u which represents microseconds (up to six of them).

DateTime::format does however use the same strings as the standard date But the following is taken from the docs about the u in outputting the data:

Microseconds (added in PHP 5.2.2). Note that date() will always generate 000000 since it takes an integer parameter, whereas DateTime::format() does support microseconds.

Upvotes: 0

octern
octern

Reputation: 4868

Use PHP's strtotime() to convert the sql date into unix time, then use date() to reformat it however you want. =strtotime("2012-08-20 06:23:28.214") works, so it's just a matter of using string manipulation to change that final colon into a decimal point.

Alternately, this blog post has code that you can use to have MS SQL Server return the unix time directly. The key function is SELECT DATEDIFF(second, '1970/01/01 00:00:00', @date).

If you are in fact using MySQL and not SQL Server, you can just use the function UNIX_TIMESTAMP() to return the current unix time.

Upvotes: 1

Related Questions