Convert Unix Time Stamp

Posted on 2004-08-18
Last Modified: 2008-02-01

I have a table in a SQL Server 2000 Database. One of the columns holds Unix Time Stamp values. Is there a way to convert the time stamp to a viewer friendly version?




08 / 17 / 2004

Thanks in advance...
Question by:prodier
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 17

Expert Comment

ID: 11832945
how are you getting that date from 1092737700?
LVL 37

Accepted Solution

gregoryyoung earned 500 total points
ID: 11833912
DATEADD(SECOND, columnname, '01/01/1970') AS expr1
LVL 37

Expert Comment

ID: 11833924
unix timestamps are seconds since standard epoch.
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

LVL 17

Expert Comment

ID: 11833954
yuck, lol
LVL 37

Expert Comment

ID: 11833991
if you wanted to do it in code the operation would be the same ...

to GET a unix timestamp from a Date ...

            static private int UnixTimeStamp(DateTime d) {
                  TimeSpan t = d.Subtract(DateTime.Parse("1/1/1970"));
                  return (int) Math.Floor(t.TotalSeconds) ;
LVL 20

Expert Comment

ID: 11840112

// calendar reference, at
const int NumDaysTo10000 = 3652059;
const int NumDaysTo1970 = 719162;

const long NumTicksPerDay = 864000000000;
const long NumTicksPerSecond = 10000000;

const long MaxTicks = NumDaysTo10000 * NumTicksPerDay -1;
const long MinTicks = 0;

// number of 100 nanosecond units from 1/1/1601 to 1/1/1970
Int64 TicksTo1970   = NumDaysTo1970 * NumTicksPerDay;

// number of seconds elapsed in Unix epoch timestamp
int elapsedEpoch = 1092737700;

// number of 100 nanosecond units from 1/1/1970 to now
Int64 TicksFrom1970 = elapsedEpoch * NumTicksPerSecond;

long universalTicks = TicksTo1970 + TicksFrom1970;
DateTime univDT = new DateTime(universalTicks);

// display elapsedEpoch in UTC

TimeZone tz = TimeZone.CurrentTimeZone;
long localTicks = universalTicks + tz.GetUtcOffset(univDT).Ticks;

if ((ulong)localTicks>(ulong)MaxTicks)
      if (localTicks>MaxTicks)
            localTicks = MaxTicks;
            localTicks = MinTicks;

// Display in local time
Console.WriteLine( new DateTime(localTicks) );

This site provides unix timestamp to readable timestamp



Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question