April 17, 2019 at 4:17 pm
I'm working with a database where a column that includes duration in terms of minutes is stored as datetime. The data looks like this. Note the '1899-12-30' info can be effectively ignored.
Duration
1899-12-30 00:56:33.000
1899-12-30 00:26:27.000
1899-12-30 01:04:02.000
1899-12-30 00:13:30.000
1899-12-30 01:10:06.000
1899-12-30 00:23:02.000
1899-12-30 00:00:06.000
I am trying to do something like this:
SELECT SUM(duration) FROM MyTable
But I get the error "Operand data type datetime is invalid for sum operator."
What should I do to sum the minutes?
April 17, 2019 at 4:39 pm
SELECT SUM(DATEDIFF(MINUTE, '1899-12-30', duration)) AS duration_in_mins
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 17, 2019 at 5:09 pm
I'm working with a database where a column that includes duration in terms of minutes is stored as datetime. The data looks like this. Note the '1899-12-30' info can be effectively ignored. Duration 1899-12-30 00:56:33.000 1899-12-30 00:26:27.000 1899-12-30 01:04:02.000 1899-12-30 00:13:30.000 1899-12-30 01:10:06.000 1899-12-30 00:23:02.000 1899-12-30 00:00:06.000 I am trying to do something like this:
SELECT SUM(duration) FROM MyTableBut I get the error "Operand data type datetime is invalid for sum operator." What should I do to sum the minutes?
What format do you want the sum to be displayed as? Also, where is the data coming from? I ask the latter question because durations listed in SQL Server normally come out as something >= '1900-01-01' for the date portion of a duration.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 17, 2019 at 5:35 pm
Thanks for the messages folks. I managed to get the results I was after with the suggestion from ScottPletcher.
Jeff - the data is coming a Microsoft Excel sheet which is being imported with SSMS/Tasks/Import Data/
In the original excel sheet it appears the format is 00/01/1900, not sure why 1899-12-30 is being inserted. The excel sheet is a data output from a website, so maybe an AccessDB at the source as rVadim suggested - I have no idea really.
April 17, 2019 at 5:38 pm
and sorry regarding
What format do you want the sum to be displayed as?
, I was able to figure out how to format the way I wanted by searching online but the answer is HH:MM:SS. I did that with this code which I found online:
SELECT CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, '1899-12-30', duration), 0), 114) From MyTable
April 17, 2019 at 5:52 pm
Sorry, I thought you only wanted it down to the minute based on your initial description
duration in terms of minutes
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 17, 2019 at 6:03 pm
and sorry regarding
Jeff Moden wrote:What format do you want the sum to be displayed as?
, I was able to figure out how to format the way I wanted by searching online but the answer is HH:MM:SS. I did that with this code which I found online:
SELECT CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, '1899-12-30', duration), 0), 114) From MyTable
That will work for up to 23:59:59. If it goes past 24 hours, it will silently fail and give you an incorrect answer. Do you need something that will handle more than 24 hours?
Also, Excel and SQL Server both use date serial numbers behind the scenes. The "0" date for SQL Server is '1900-01-01". Why they decided to do it differently for Excel is beyond me and it has been that way since before I can remember. Because they didn't actually know how to correctly calculate leaps years in Excel, they have come up with a couple of "base" dates for Excel rather than repair the actual problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 17, 2019 at 6:22 pm
That will work for up to 23:59:59. If it goes past 24 hours, it will silently fail and give you an incorrect answer. Do you need something that will handle more than 24 hours?
Ah - that probably explains some of the weird data I'm seeing in the tests I'm doing right now 🙂 Yes if you have something that would be terrific, thank you.
April 18, 2019 at 12:02 am
This will do it for you. Details are in the comments.
-- DROP TABLE #MyTable
--===== Put the test data into a table.
-- this is NOT a part of the solution.
SELECT Duration = CONVERT(DATETIME,d.Duration)
INTO #MyTable
FROM (VALUES
('1899-12-30 00:56:33.000')
,('1899-12-30 00:26:27.000')
,('1899-12-30 01:04:02.000')
,('1899-12-30 00:13:30.000')
,('1899-12-30 01:10:06.000')
,('1899-12-30 00:23:02.000')
,('1899-12-30 00:00:06.000')
)d(Duration)
;
--===== Solve the problem with a bit of direct date math
WITH
cteDateSum (Duration) AS
(--==== Keep it "DRY" by doing this calculation just once
-- and we'll reuse it in the outer SELECT
SELECT CONVERT(DATETIME,SUM(CONVERT(FLOAT,Duration+2))) -- The direct date math.
FROM #MyTable
)
SELECT LEFT(DATEDIFF(hh,0,Duration),10) --Calcs hours up to limits of DATETIME
+ RIGHT(CONVERT(CHAR(8),Duration,108),6) --Calculates the :MI:SS part
FROM cteDateSum
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2019 at 6:19 pm
Thanks Jeff! I'm not an OP but still...
And if we want to get days out of it and keep hours within 24 then last SELECT would be something like this:
--Days HH:MI:SS
SELECT LEFT(DATEDIFF(dd,0,Duration),10) + ' '
+ CONVERT(CHAR(8),Duration,108)
FROM cteDateSum
--Vadim R.
April 18, 2019 at 6:51 pm
I personally wouldn't use "+2", as it's nebulous unless you already know what it's doing; instead, I'd use the actual base date. And, if you shift from Excel, the base date might even change on its own. I put a "d" (for days) in the output just because it seems clearer to me:
1d 11:10:08
06:14:23
3d 00:03:45
WITH cte_constants AS (
SELECT CAST('1899-12-30' AS date) AS base_date, 60*60*24 AS seconds_in_a_day
),
cte_calc_total_duration AS (
SELECT SUM(DATEDIFF(SECOND, base_date, duration)) AS total_seconds
FROM dbo.your_table_name
CROSS JOIN cte_constants
)
SELECT CASE WHEN total_seconds < seconds_in_a_day THEN ''
ELSE CAST(total_seconds / seconds_in_a_day AS varchar(5)) + 'd ' END +
CONVERT(varchar(8), DATEADD(SECOND, total_seconds % seconds_in_a_day, 0), 8)
FROM cte_calc_total_duration
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 18, 2019 at 8:13 pm
Thank you all very much indeed!
April 18, 2019 at 8:33 pm
FYI, just in case you care, I was able to get my code fully corrected.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 18, 2019 at 9:51 pm
Thank you all very much indeed!
Just in case you want to separate the days from the HH:MI:SS stuff and as rVadim suggests, the change is quite simple.
--===== Solve the problem with a bit of direct date math
WITH
cteDateSum (Duration) AS
(--==== Keep it "DRY" by doing this calculation just once
-- and we'll reuse it in the outer SELECT
SELECT CONVERT(DATETIME,SUM(CONVERT(FLOAT,Duration+2))) -- Direct date math.
FROM #MyTable
)
SELECT LEFT(CONVERT(INT,Duration),10) --Calcs Days
+ 'd ' --Days identifier/delimiter. Change to suit yourself
+ CONVERT(CHAR(8),Duration,108) --Calculates the HH:MI:SS part
FROM cteDateSum
;
If you intend to use this method for different "base dates", you could pass it in as a whole date, do a simple DATEDIFF between that base date and the base date of SQL Server ("0" or '1900" or '19000101', pick your poison), and use that to replace the "+2" in the formula.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply