question

NAmola avatar image
NAmola asked

Date time in SQL Server

I have a field in one of the tables, time taken. Ideally it should just have time in it but since its being calculated by taking the difference of fields starting time and ending time, it has the date part as well (yyyy-mm-dd hh:mm: ss.ms). Now I need to calculate the total time taken where I need to add the time part in all the values. How to do that? I expect the seconds parts to get added, divided by 60 to get the minutes and then added to minutes, which in turn get added, divided by 60 to get hours and then get added to hours. Is there an inbuilt function for it?
datetime
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What's the data type of this field?
0 Likes 0 ·
NAmola avatar image NAmola commented ·
As of now i am using this query to calculate total time taken SELECT CONVERT(VARCHAR,HOURS)+':'+CONVERT(VARCHAR,MINUTES)+':'+CONVERT(VARCHAR,SECONDS) AS TotalTimeTaken FROM (SELECT SUM(DATEPART(HH,ColumnName))+(SUM(DATEPART(MM,ColumnName))+SUM(DATEPART (SS,ColumnName))/60)/60 AS HOURS, (SUM(DATEPART(MM,ColumnName))+SUM(DATEPART (SS,ColumnName))/60)%60 AS MINUTES, SUM(DATEPART (SS,ColumnName))%60 AS SECONDS FROM TableName)Temp But I am looking for something simpler.
0 Likes 0 ·
NAmola avatar image NAmola commented ·
its datetime
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
As Thomas pointed out, we need to know the datatypes, but if you are using some variation of a date or datetime field, then you may want to look at datediff and dateadd. There is information on various date functions here: [ http://msdn.microsoft.com/en-us/library/ms186724.aspx][1] [1]: http://msdn.microsoft.com/en-us/library/ms186724.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Raj More avatar image
Raj More answered
It looks like you are saving the amount of time used in a datetime column called ColumnName. So you probably have data that shows up like this ColumnName ------------------ 1900-01-01 10:00:00.000 1900-01-01 20:00:00.000 1900-01-01 00:10:00.000 1900-01-01 00:20:00.000 1900-01-01 00:00:10.000 1900-01-01 00:00:20.000 You don't have to break down the hours minutes and seconds to sum it all up. You can simply sum the entire column and then do your transformation at the end. Here is an example SELECT CAST (Datepart (D, TotalTime) AS VarChar) + ' days, ' + CAST (Datepart (hh, TotalTime) AS VarChar) + ' hours, ' + CAST (Datepart (mi, TotalTime) AS VarChar) + ' minutes, ' + CAST (Datepart (mi, TotalTime) AS VarChar) + ' seconds.' FormatOne, CAST (DateDiff (hh, '1900-01-01', TotalTime) AS VarChar) + ' hours, ' + CAST (Datepart (mi, TotalTime) AS VarChar) + ' minutes, ' + CAST (Datepart (mi, TotalTime) AS VarChar) + ' seconds.' FormatTwo FROM ( SELECT DATEADD (S, SUM (DATEDIFF (S, '1900-01-01', ColumnName)), '1900-01-01') TotalTime FROM ( SELECT CONVERT (DATETIME, '1900-01-01 10:00:00.000') ColumnName UNION SELECT '1900-01-01 20:00:00.000' UNION SELECT '1900-01-01 00:10:00.000' UNION SELECT '1900-01-01 00:20:00.000' UNION SELECT '1900-01-01 00:00:10.000' UNION SELECT '1900-01-01 00:00:20.000' ) AllTimestamps ) TotalTime The output is FormatOne FormatTwo ---------------------------------------- ------------------------------- 2 days, 6 hours, 30 minutes, 30 seconds. 30 hours, 30 minutes, 30 seconds.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.