Convert Unix Timestamp into MS SQL datetime
Results 1 to 4 of 4

Thread: Convert Unix Timestamp into MS SQL datetime

  1. #1
    Junior Member
    Join Date
    Nov 2006

    Convert Unix Timestamp into MS SQL datetime

    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.

  2. #2
    Senior Member
    Join Date
    Jan 2002
    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:
    PHP Code:
    declare @UNIX_TIMESTAMP int
    @UNIX_TIMESTAMP 1178225057
    select  dateadd
    (ss,@UNIX_TIMESTAMP,'01/01/1970') as theDateTime

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

  3. #3
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    If you want to do it via PHP:
    PHP Code:
    $sqlDateTime date('Y-m-d H:i:s'$unixTimeStamp); 
    Then use $sqlDateTime in your SQL:
    PHP Code:
    $sql "INSERT INTO table_name (date_time_column) VALUES ('$sqlDateTime')"
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~

  4. #4
    Senior Member Roger Ramjet's Avatar
    Join Date
    Jul 2004
    Leeds, UK
    Is this MS SQL or MySQL?

    In mysql you have the sql function FROM_UNIXTIME() to do it for you in the query
    PHP Code:
    $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.

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts