I’m a forty-something Microsoft SQL Server DBA of 15+ years, a devoted husband, and a father of three young boys. I have been a DBA at a public university, at a major bank, at a healthcare system, and I now work as a remote DBA with customers across the United States. I write and speak primarily about the tips and tricks that I discover along my SQL Server journey.

### Why Is your datetime saved as a bigint anyway?

Yet another issue I had never seen before - thankfully this time I was able to find a relatively quick answer.

On a client system their primary database had grown to throw disk alarms on the DATA drive, so I was alerted.  I signed onto the system and found that the largest table in the 43GB database was itself 20GB.  I looked at the contents of the table and found several fields labelled sent_time and audit_time...only to find that they were bigints rather than datetime - yuck!

The best description of the situation I found was a post from Ben Northway (Blog/@northben) titled "SQL Server date time conversions to/from Unix bigint format UPDATED".  Ben describes how the bigint datetime I found is actually a UNIX-style timestamp, counting the milliseconds since January 1, 1970. (makes sense, doesn't it?)  It even has a catchy name - Epoch time.

Why January 1, 1970?  Here's one response I found on a StackOverflow post asking this same question:

The universe was created on Jan 1, 1970. Anyone who tells you otherwise is clearly lying. –  ijw Jul 7 '09 at 23:52
...and now you know.

Ben's formula worked perfectly for me:

As you can see, rather than performing match in milliseconds, the formula divides by 1000 in order to do math in seconds - as Ben notes this prevents an arithmetic overflow error.

A couple of limitations of this approach:
• Epoch Time ignores leap seconds, so if you need that level of precision you will need a much much more complicated formula than this.
• This formula is based on UTC/GMT, so if you need to convert to a specific time zone you need to add in a correcting factor like this: