Solved

# Formatting/converting date/time that is in milliseconds

Posted on 2005-03-08
Medium Priority
512 Views
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
Question by:latzo4
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 34

Expert Comment

ID: 13492248
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

LVL 75

Accepted Solution

Anthony Perkins earned 1000 total points
ID: 13492761
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

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
0

LVL 8

Expert Comment

ID: 13495036
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

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month8 days, 8 hours left to enroll

#### 764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.