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.
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
High Energy Magic Dept.
If you want to do it via PHP:
Then use $sqlDateTime in your SQL:
$sqlDateTime = date('Y-m-d H:i:s', $unixTimeStamp);
$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 he’s a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html
Is this MS SQL or MySQL?
In mysql you have the sql function FROM_UNIXTIME() to do it for you in the query
I'm not that up on MS SQL functions but guess there may be an equivalent.
$sql = "INSERT INTO table_name (date_time_column) VALUES (FROM_UNIXTIME($unixTimeStamp))";
Users Browsing this Thread
There are currently 2 users browsing this thread. (0 members and 2 guests)