Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.

Format of Dates in Database


I need to write a query that returns the create date and update date. These dates are stored as integers and I cannot tell what the format is by looking at it. Can someone please tell me how I can convert these integers into a mm/dd/year hh:mm format?


They are Epoch format - used extensively by Unix. It is the # of seconds since 1/1/1970. Here is a sample:

SELECT as Group_Name, as CaseID, as SuiteId, as SectionName,
c.title as Case_Title, as Suite_Name, as UserName, as ProjectName, c.updated_by, c.user_id,,,
DATEADD(ss, c.created_on , ‘1/1/1970’) AS CreateDate,
DATEADD(ss, c.updated_on , ‘1/1/1970’) AS UpdateDate,
c.created_on, c.updated_on,

This is how to get the current date in Epoch format:
EpochDate = DateDiff(“S”, “1/1/1970”, Now())

I use the above in a VBA userform but I believe it should work on SQL as well.


This is exactly what I needed. Thanks!


No problem - glad to help.


What time zone is used for storing dates in SQL DB?


Hello Alex,

Timestamps are stored in Unix timestamp format which are the seconds since 1970 midnight UTC:

Almost all programming languages have support for this format and it’s usually easy to convert to the date/time structure.

I hope this helps!