?
Solved

Formatting/converting date/time that is in milliseconds

Posted on 2005-03-08
3
Medium Priority
?
512 Views
Last Modified: 2011-08-18
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
Comment
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
3 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
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

by:
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

by:Julianva
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.

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.

Join & Ask a Question