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')"
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  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 1 users browsing this thread. (0 members and 1 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