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.
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)
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.
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.
