Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft SQL Server: Programming FAQ

T-SQL Hints and Tips

Converting UNC/Unix/Oracle Time to readable date field. by mrdenny
Posted: 6 Dec 04 (Edited 20 Aug 05)

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.


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.


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

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close