February 28, 2016 at 4:12 am
Hello!
I am trying to extract the hh:mm:ss element of a datetime column of date. The date time column has the following format "dd:mm:yyyy hh:mm:ss".
I used the convert(varchar (8), MyColumnName, 108) to extract the time element, and works*.
*However, when I compare the original DateTime column of data against my new time column, some of the times are a second out. For example, the datetime column says "13/01/2016 09:43:40" but in the time column it is "09:43:39".
It's really important for my project that I extract the exact time displayed in the TimeDate column, as pedantic as it sounds, it can't be 1 second out. Is it rounding up milliseconds or something?
Any ideas please? I've not come across this before
February 28, 2016 at 5:25 am
If the data type of the column is datetime, then it stores fractional seconds as well, with a precision of 1/300th of a second. The CONVERT with style 108 that you uses simply trims away the fractional seconds.
You do not specify what method you use for the alternative display of the time, but I suspect that this is a method that uses rounding - so when the fractional part is between 0.5 and 0.997 seconds, it will round up. This would explain the 1-second difference you see.
February 28, 2016 at 6:45 am
Thanks Hugo, I thought something must be rounding up somewhere. Is there a way to stop this rounding up please?
I should add, the SQL I am using is being executed via VB for an Excel based report. Perhaps I can do something different with the SQL and then perhaps set up my Excel template so that is stops the rounding?
Usually, i'd say in Excedl =INT(A1) to get the time of the datetime but I don't know how to do that via SQL. Doing the =int (a1) method stops the rounding up but I need it to do it via SQL.
February 28, 2016 at 7:13 am
The code that extracts the time is not rounding up, it is truncating. It is the other display (Excel, if I understand your reply correctly) that does the rounding.
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). And depending on how exact your timings need to be, consider switching to datetime2, which supports precision up to 100 nanoseconds (0.0000001 second).
If you need to show seconds and you need consistency, then the best choice is to ensure that the data in the table does not have sub-second precision. Again, possible with datetime2 by setting the precision to zero.
If you want the Excel data to match what your current query for time only does, then either use the same logic when exporting to Excel, or request help on an Excel forum. I cannot help you with that.
If you want your query to match what Excel does, then first ensure that Excel does indeed round up from 0.5 seconds and up. If that is the case, then you can mimic that in SQL Server by adding 500 ms (using DATEADD) to the data before converting. Adding 0.5 seconds and then truncating is the same as rounding.
February 28, 2016 at 12:45 pm
Thanks for that again. I'll give it a try in the morning and let you know the outcome.
J 🙂
February 28, 2016 at 12:55 pm
Hugo's approach of adding 500 ms and then truncating will most certainly work to round the datetime to the nearest second. However, ask yourself what the purpose is behind querying the data to begin with. If you want it to match Excel, is that because Excel has been deemed to be correct? The point is that whatever you're going to report, you want it to be accurate.
If you want to calculate a duration between two dates, you might want to look at using DATEDIFF in SQL and then then copying the calculated duration to Excel. This would eliminate the truncation completely and your duration's precision would be that of a datetime.
This is just another approach. Of course, it depends on your requirements.
February 28, 2016 at 1:17 pm
Thank you for that. My biggest issue is i'm calling from a database I don't have full access to so it's difficult for me to know what format the data was stored in originally. I'm not even sure if the db stored milliseconds but based on the above, it seems it does.
The column that holds the datetime data is called StartDateTime. What code could I use in SQL to tell it to bring through the milliseconds so I could check that it is being recorded?
Thanks for the help guys.
February 28, 2016 at 1:54 pm
In a query tool such as SSMS or sqlcmd.exe, execute a query such as SELECT StartDateTime FROM YourTableName;
These tools present the data in a very raw fashion, so you will see what is stored (though in a human-consumable format, if it actually is datetime the internal storage is quite different).
To find the data type of the column, you can also use the object explorer in SQL Server Management Studio if you have access to it (and if you don't, request it; you cannot work seriously with SQL Server databases without that tool, and it's a free download anyway) and the proper permissions to the database. In other query tools, you can either query the system views such as sys.tables and sys.objects, or you can run the statement "EXEC sp_help 'TableName';" Again, you need some permissions for this, but if you don't have permission to examine the schema of the database, you cannot do your job anyway.
February 28, 2016 at 7:08 pm
jimtimber (2/28/2016)
Hello!I am trying to extract the hh:mm:ss element of a datetime column of date. The date time column has the following format "dd:mm:yyyy hh:mm:ss".
I used the
convert(varchar (8), MyColumnName, 108)to extract the time element, and works*.*However, when I compare the original DateTime column of data against my new time column, some of the times are a second out. For example, the datetime column says "13/01/2016 09:43:40" but in the time column it is "09:43:39".
It's really important for my project that I extract the exact time displayed in the TimeDate column, as pedantic as it sounds, it can't be 1 second out. Is it rounding up milliseconds or something?
Any ideas please? I've not come across this before
Try this instead of going straight to a string:
DECLARE @D DATETIME = '2016-02-28 19:04:51.997';
SELECT CAST(CAST(@D AS TIME(3)) AS VARCHAR(8));
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 28, 2016 at 9:03 pm
Orlando Colamatteo (2/28/2016)
Try this instead of going straight to a string:
DECLARE @D DATETIME = '2016-02-28 19:04:51.997';SELECT CAST(CAST(@D AS TIME(3)) AS VARCHAR(8));
It returns exactly the same result as the original conversion:DECLARE @D DATETIME = '2016-02-28 19:04:51.997';
SELECT convert(varchar (8), @D, 108)
--------
19:04:51
_____________
Code for TallyGenerator
February 28, 2016 at 9:40 pm
Sergiy (2/28/2016)
Orlando Colamatteo (2/28/2016)
Try this instead of going straight to a string:
DECLARE @D DATETIME = '2016-02-28 19:04:51.997';SELECT CAST(CAST(@D AS TIME(3)) AS VARCHAR(8));
It returns exactly the same result as the original conversion:
DECLARE @D DATETIME = '2016-02-28 19:04:51.997';SELECT convert(varchar (8), @D, 108)
--------19:04:51
In the original post it sounded like things were being rounded down in the change over to TIME, not up, when compared to the original DATETIME value which does not really compute.
I still am unclear as to what "the date time column" and "the new time column" actually are or where they are referenced, resultset, table, Excel...
All I know is using CONVERT with a Style is a rabbit trail so I try to use standard data types and CAST where possible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 28, 2016 at 10:56 pm
Orlando Colamatteo (2/28/2016)
Sergiy (2/28/2016)
Orlando Colamatteo (2/28/2016)
Try this instead of going straight to a string:
DECLARE @D DATETIME = '2016-02-28 19:04:51.997';SELECT CAST(CAST(@D AS TIME(3)) AS VARCHAR(8));
It returns exactly the same result as the original conversion:
DECLARE @D DATETIME = '2016-02-28 19:04:51.997';SELECT convert(varchar (8), @D, 108)
--------19:04:51
In the original post it sounded like things were being rounded down in the change over to TIME, not up, when compared to the original DATETIME value which does not really compute.
I still am unclear as to what "the date time column" and "the new time column" actually are or where they are referenced, resultset, table, Excel...
Yeah, I'm puzzled too.
_____________
Code for TallyGenerator
February 29, 2016 at 6:47 am
when I compare the original DateTime column of data
Where are you viewing the original value, if in Excel then check the formatting of the cell as Excel will round if the cell is set to time format.
Far away is close at hand in the images of elsewhere.
Anon.
February 29, 2016 at 7:53 am
In the original post it sounded like things were being rounded down in the change over to TIME, not up, when compared to the original DATETIME value which does not really compute.
I still am unclear as to what "the date time column" and "the new time column" actually are or where they are referenced, resultset, table, Excel...
Yes, sorry, it was rounding down, not up.
I have spoken with the db designer today and explained my issue and he said it's an issue with Excel not being able to hold the format dd/mm/yyyy hh:mm:ss:miliseconds in one column.
His suggestion is to get the SQL code to turn the whole time stamp into milliseconds and then recalculate the hh:mm:ss:miliseconds from that, as Excel will let me have hh:mm:ss:miliseconds format. I'm not sure exactly how to do that in SQL (he said something about datedif)! If I try to do it via VB or Excel, it will have already lost the milliseconds so must be done in SQL. For security reasons, I'm not allowed to use Management Studio so I have to do all my SQL freehand and then run it via VB. Not fun!
A bit more info on my project: when a phone call was made in my company, a datetime field is inserted into the DB table. I am trying to analyse how long it took to get to certain stages of each phone call from when the call was made. As little as a 1/2 second out might not seem much, but, padded out over 200 staff making 100+ calls a day, the 1/2 seconds start to add up.
Thanks for all your help. I'm not usually stumped like this 🙂
February 29, 2016 at 8:09 am
jimtimber (2/29/2016)
In the original post it sounded like things were being rounded down in the change over to TIME, not up, when compared to the original DATETIME value which does not really compute.
I still am unclear as to what "the date time column" and "the new time column" actually are or where they are referenced, resultset, table, Excel...
Yes, sorry, it was rounding down, not up.
I have spoken with the db designer today and explained my issue and he said it's an issue with Excel not being able to hold the format dd/mm/yyyy hh:mm:ss:miliseconds in one column.
His suggestion is to get the SQL code to turn the whole time stamp into milliseconds and then recalculate the hh:mm:ss:miliseconds from that, as Excel will let me have hh:mm:ss:miliseconds format. I'm not sure exactly how to do that in SQL (he said something about datedif)! If I try to do it via VB or Excel, it will have already lost the milliseconds so must be done in SQL. For security reasons, I'm not allowed to use Management Studio so I have to do all my SQL freehand and then run it via VB. Not fun!
A bit more info on my project: when a phone call was made in my company, a datetime field is inserted into the DB table. I am trying to analyse how long it took to get to certain stages of each phone call from when the call was made. As little as a 1/2 second out might not seem much, but, padded out over 200 staff making 100+ calls a day, the 1/2 seconds start to add up.
Thanks for all your help. I'm not usually stumped like this 🙂
DATEDIFF is the function used to calculate the time elapsed between two datetime values. Example:
SELECT Elapsed = DATEDIFF(ms, start, end)
FROM dbo.table_name;
The function is in Books Online at https://msdn.microsoft.com/en-us/library/ms189794%28v=sql.100%29.aspx if you need details on any part of it.
The parameters are:
ms = Type type of difference to calculate. ms is milliseconds
start = Start date.
end = End date.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply