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

# 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?

thanks

Lat
0
latzo4
1 Solution

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.
0

Commented:
BriCrowe,

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

latzo4,
See if this works for you:

Declare @d as bigint
Set @d = 1093048298056

This outputs (it is off by 1 millisecond):
2004-08-21 00:31:38.057
0

Commented:
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'