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.

    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

    🆒

      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')";

        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.

          Write a Reply...