Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Converting UNC/Unix/Oracle Time to readable date field.

T-SQL Hints and Tips

Converting UNC/Unix/Oracle Time to readable date field.

by  mrdenny  Posted    (Edited  )
Here is a code sample to show how to convert UNC or Unix Time to a format readable by people. Take the UNC time value, in this case 1101859200. Devide that number by 86100. Take that value and add it to Jan 1, 1970.
Code: 
declare @UnixTime bigint
set @UnixTime = 1101859200

select dateadd(dd, (@UnixTime/86400), '1/1/1970')
This gives us the resule of 1/12/2004.

If you need to get the time as well this code will do the trick.
Code: 
declare @UnixTime bigint
set @UnixTime = 1101859754

select dateadd(ss, ((convert(numeric(24,6), @UnixTime)/86400)-(@UnixTime/86400))*86400, dateadd(dd, (@UnixTime/86400), '1/1/1970'))
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top