Convert Unix Timestamp into MS SQL datetime

nakedsole

I'm trying to convert a Unix timestamp in a format to insert into a MS SQL datetime column (2007-05-03 16:38:04.620). Any ideas? I can insert the current datetime with getdate() but I have not been able to find out how to convert a Unix timestamp into the correct format.

tomhath

It's easy. A Unix timestamp is the number of seconds since 01/01/1970. So just add the timestamp number of seconds to that date:

declare @UNIX_TIMESTAMP int
select @UNIX_TIMESTAMP = 1178225057
select  dateadd(ss,@UNIX_TIMESTAMP,'01/01/1970') as theDateTime

select datediff(ss, '01/01/1970', getdate()) as theTimestamp

🆒

NogDog

If you want to do it via PHP:

$sqlDateTime = date('Y-m-d H:i:s', $unixTimeStamp);

Then use $sqlDateTime in your SQL:

$sql = "INSERT INTO table_name (date_time_column) VALUES ('$sqlDateTime')";

Roger_Ramjet

Is this MS SQL or MySQL?

In mysql you have the sql function FROM_UNIXTIME() to do it for you in the query

$sql = "INSERT INTO table_name (date_time_column) VALUES (FROM_UNIXTIME($unixTimeStamp))";

I'm not that up on MS SQL functions but guess there may be an equivalent.