• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

Formatting/converting date/time that is in milliseconds

I have a table that has the following fields:

[user]    [date_time]
1           1093048298056

the date time looks like it's in milliseconds (bigint), how do I convert it in a query into "mm/dd/yyyy" and why do they do that? Is it more accurate or something?


1 Solution
Brian CroweDatabase AdministratorCommented:
who's they?

a datetime is stored in 2 4-byte segments.  the first segment stores the number of days since 1/1/1900 and the second stores the number of milliseconds since midnight.

I don't know of a canned function that converts a bigint value in milliseconds to a datetime but you could certainly write one.  First you need to know the epoch date (the date you start counting from).  You could probably work backwards if you knew what date a given value was supposed to represent.
Anthony PerkinsCommented:

>>who's they?<<
I suspect it is a UNIX date format.

See if this works for you:

Declare @d as bigint
Set @d = 1093048298056

select DATEADD(millisecond, @d % 1000, DATEADD(second, @d / 1000, '1970-01-01'))

This outputs (it is off by 1 millisecond):
2004-08-21 00:31:38.057
Take that date format and drop it into an excel
spreadsheet - see what happens - it should
convert it to a proper date - In Sql this is done to conserve space.

Declare @d as varchar(10)
Set @d = '1093048298056'

select DATEADD(millisecond, @d % 1000, DATEADD(second, @d / 1000, '1970-01-01'))

Result - 1970-01-13 15:37:28.297

If you get the same result in Excell then this is correct
Sql does not support BIGint - I am using server 7
Run this query as it is


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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now