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:
g.name as Group_Name, c.id as CaseID, s.name as SuiteId, s.name as SectionName,
c.title as Case_Title, su.name as Suite_Name, u.name as UserName,
p.name as ProjectName, c.updated_by, c.user_id, u.name, u1.name,
DATEADD(ss, c.created_on , ‘1/1/1970’) AS CreateDate,
DATEADD(ss, c.updated_on , ‘1/1/1970’) AS UpdateDate,
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?
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!