February 29, 2016 at 8:25 am
Thanks for that, its similar toExcel's datedif fuction (just about). I can use that to work out the exact difference between the different stages of the calls.
Can I please ask, what would the code be to convert just the time part of a datetime entry to miliseconds. So, for example if SQL has this: 01/01/2016 13:23:42:985, what should I use to convert the 13:23:42:985 to miliseconds in SQL.
Thanks again, I've spent all weekend looking at this!!
February 29, 2016 at 8:27 am
jimtimber (2/29/2016)
Thanks for that, its similar toExcel's datedif fuction (just about). I can use that to work out the exact difference between the different stages of the calls.Can I please ask, what would the code be to convert just the time part of a datetime entry to miliseconds. So, for example if SQL has this: 01/01/2016 13:23:42:985, what should I use to convert the 13:23:42:985 to miliseconds in SQL.
Thanks again, I've spent all weekend looking at this!!
You can use DATEPART for that.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 29, 2016 at 8:36 am
DATEPART(hh,'01/01/2016 13:23:42:985')*60*60*1000
+DATEPART(mi,'01/01/2016 13:23:42:985')*60*1000
+DATEPART(ss,'01/01/2016 13:23:42:985')*1000
+DATEPART(ms,'01/01/2016 13:23:42:985')
Far away is close at hand in the images of elsewhere.
Anon.
February 29, 2016 at 8:47 am
Question:
Do you want to convert the different datetime values to ms, export them to Excel and then calculate the difference?
Or do you want to export the differences in ms to Excel?
Either way will get you to where you want to be.
February 29, 2016 at 9:32 am
Hugo Kornelis (2/28/2016)
You write that it is important for your project to show the exact time - so why not stop the rounding/truncating completely and show milliseconds as well? (Note that datetime actually has a 1/300 second precision, so it will show as 0:00:00.000, 0:00:00.003, 0:00:00.005, 0:00:00.007, 0:00:00.010, etc).
Datetime has a precision of 3/1000 of a second. But the spacing between times is not equal. The times are:
0:00:00.000, 0:00:00.003, 0:00:00.007, 0:00:00.010
select '--' [--], convert(datetime,'20160229 23:59:59.997') whichtime
UNION ALL
select '--' [--], convert(datetime,'20160229 23:59:59.998')
UNION ALL
select '--' [--], convert(datetime,'20160229 23:59:59.999')
-------------- results into:
--whichtime
--2016-02-29 23:59:59.997
--2016-02-29 23:59:59.997
--2016-03-01 00:00:00.000
Excel uses the fraction of a day to represent time. This is different from SQLserver, so potentialli there is a problem.
For example 08:00:00 can be represented in in sqlserver exactly. In Excel it is represented as : 0.3333333333333330000000. This is almost but not totaly exact 8 hours.
Some rounding is done automatically, so most times it works out, but there might be problems.
Excel today 08:00 is : 42,429.3333333333000000000000
If you substract today; 0.333333333335759000000000 is left.
This is a tad short of 08:00.
Rounding this might lead to a small difference.
Always be carefull when using 'floats' and datetime's and doing exact comparisons.
Ben
February 29, 2016 at 9:49 am
I will try all the above tomorrow morning and feedback. Genuinely, thanks for all the help so far to all of you.
February 29, 2016 at 1:29 pm
jimtimber (2/29/2016)
Can I please ask, what would the code be to convert just the time part of a datetime entry to miliseconds. So, for example if SQL has this: 01/01/2016 13:23:42:985, what should I use to convert the 13:23:42:985 to miliseconds in SQL.
David's answer should work. Or you can use this (which computes the difference between the full datetime and the same datetime cast to date (which truncates the time part):
DATEDIFF(ms, CAST(@YourDateTime AS date), @YourDateTime)
(Where @YourDateTime is a variable that holds the datetime, but you can also use a column name, an expression, or anything else in its place)
February 29, 2016 at 1:35 pm
Hugo Kornelis (2/29/2016)
jimtimber (2/29/2016)
Can I please ask, what would the code be to convert just the time part of a datetime entry to miliseconds. So, for example if SQL has this: 01/01/2016 13:23:42:985, what should I use to convert the 13:23:42:985 to miliseconds in SQL.David's answer should work. Or you can use this (which computes the difference between the full datetime and the same datetime cast to date (which truncates the time part):
DATEDIFF(ms, CAST(@YourDateTime AS date), @YourDateTime)
(Where @YourDateTime is a variable that holds the datetime, but you can also use a column name, an expression, or anything else in its place)
If the date being compared to @YourDateTime in Excel crosses into a new day, won't that screw up the calculation if it's just using milliseconds? Granted, I usually do duration calculation in SQL, so I may be missing the point of doing it in Excel.
February 29, 2016 at 1:39 pm
Ed Wagner (2/29/2016)
Hugo Kornelis (2/29/2016)
jimtimber (2/29/2016)
Can I please ask, what would the code be to convert just the time part of a datetime entry to miliseconds. So, for example if SQL has this: 01/01/2016 13:23:42:985, what should I use to convert the 13:23:42:985 to miliseconds in SQL.David's answer should work. Or you can use this (which computes the difference between the full datetime and the same datetime cast to date (which truncates the time part):
DATEDIFF(ms, CAST(@YourDateTime AS date), @YourDateTime)
(Where @YourDateTime is a variable that holds the datetime, but you can also use a column name, an expression, or anything else in its place)
If the date being compared to @YourDateTime in Excel crosses into a new day, won't that screw up the calculation if it's just using milliseconds? Granted, I usually do duration calculation in SQL, so I may be missing the point of doing it in Excel.
I think Jim just asked how to use T-SQL to get the time part of a datetime as milliseconds. In other words the number of milliseconds since the last midnight.
The formula I posted can never cross a day, and it only exports an integer to Excel.
Perhaps I misunderstood Jim, in which case - my apologies.
February 29, 2016 at 1:47 pm
Hugo Kornelis (2/29/2016)
Ed Wagner (2/29/2016)
Hugo Kornelis (2/29/2016)
jimtimber (2/29/2016)
Can I please ask, what would the code be to convert just the time part of a datetime entry to miliseconds. So, for example if SQL has this: 01/01/2016 13:23:42:985, what should I use to convert the 13:23:42:985 to miliseconds in SQL.David's answer should work. Or you can use this (which computes the difference between the full datetime and the same datetime cast to date (which truncates the time part):
DATEDIFF(ms, CAST(@YourDateTime AS date), @YourDateTime)
(Where @YourDateTime is a variable that holds the datetime, but you can also use a column name, an expression, or anything else in its place)
If the date being compared to @YourDateTime in Excel crosses into a new day, won't that screw up the calculation if it's just using milliseconds? Granted, I usually do duration calculation in SQL, so I may be missing the point of doing it in Excel.
I think Jim just asked how to use T-SQL to get the time part of a datetime as milliseconds. In other words the number of milliseconds since the last midnight.
The formula I posted can never cross a day, and it only exports an integer to Excel.
Perhaps I misunderstood Jim, in which case - my apologies.
Hugo, you may very well be right. I thought it was about calculating different stages and how long it took to get from A to B to C and so on. If I was mistaken and it was about just getting the milliseconds out of a date time, forget everything I said and please accept my apology.
February 29, 2016 at 3:18 pm
Actually I can make use of both ideas for 2 seperate reports. I think for this project, converting to milliseconds is important so I can compare the different stages very accurately. For another report I'm working on, the datediff option would be fine as a second here or there isn't a big issue. This report, however, measures performance of people at work and I've got to be accurate to be fair to the people being assessedl
However, this all depends if I can get it to work in the morning! Will report back when i've made progress!
March 1, 2016 at 2:15 am
Okay, I've tried both suggestions.
Because the accuracy is important in this project, it seems David's DATEPART suggestion is the most accurate. I rang the DB admin and we went through a few rows of data. He did a calculation on MS datediff'ing to timestamps of different phases of a call. I worked backwards from the milliseconds conversions I did and we were very accurate (a couple were slightly out but nothing major and a lot more accurate than the convert(varchar code I was using).
This has been really useful for me, I feel I can do other things in my reports using what you guys suggested. So, genuinely, thank you.
(Do I have to mark this topic as solved? I have marked David's post as the solution)
March 1, 2016 at 5:11 am
jimtimber (3/1/2016)
(Do I have to mark this topic as solved? I have marked David's post as the solution)
Even that's not absolutely necessary, but certainly appreciated. I'm glad you got a solution that works for you. Thanks for the feedback.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply