Convert Sql Server (MSSQL) datetime column to Unix time in PHP

Question

Querying SQL Server (MSSQL) columns in PHP (Ubuntu / PHP 5.2.4), my date columns are coming out looking like this:

May 16 2013 12:00:00:000AM

strtotime won't parse them.

Tried this:

preg_match("/^([a-zA-Z]{3}) ([0-9]{1,2}) ([0-9]{4}) ([0-9]{2}):([0-9]{2}):([0-9]{2}):[0-9]{3}([apmAPM]{2})$/",$time_string,$regs);
$month = $regs[1];
$day = $regs[2];
$year = $regs[3];
$hour = $regs[4];
$minute = $regs[5];
$second = $regs[6];
$ampm = $regs[7];
$converted_time_string = $month." ".$day." ".$year." ".$hour.":".$minute.":".$second.$ampm;

$actual_time = strtotime($converted_time_string);

return $actual_time;

But it's having trouble with some dates, for example:

Aug 1 2013 12:00:00:000AM

Any more reliable suggestions on how to do this? Upgrading PHP not an option at this time.

Eugen Rieck · Accepted Answer · 2013-05-31 07:54:43Z

3

I recommend you use SELECT ... CONVERT(VARCHAR(19), dateColumn, 120) ... to select the date in YYYY-MM-DD HH:MM:SS (24h) format - this assures loss-free conversion with strtotime()

answered May 31, 2013 at 7:54

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaziki · Accepted Answer · 2013-05-31 07:54:28Z

0

It's possible that date_parse_from_format could parse it to a associated array. With this array you can create the new timestring.

http://www.php.net/manual/en/function.date-parse-from-format.php

answered May 31, 2013 at 7:54

Zaziki

4182 silver badges12 bronze badges

Collectives™ on Stack Overflow

Convert Sql Server (MSSQL) datetime column to Unix time in PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related