How to sum a number of rows with minute data where column format is datetime

  • caspersql

    SSCrazy

    Points: 2064

    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?

  • ScottPletcher

    SSC Guru

    Points: 98016

    SELECT SUM(DATEDIFF(MINUTE, '1899-12-30', duration)) AS duration_in_mins

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeff Moden

    SSC Guru

    Points: 993862

    caspersql wrote:

    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?

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • rVadim

    Hall of Fame

    Points: 3865

    The OP of this post suggests that 1899-12-30 is coming from Access DB.

    --Vadim R.

  • caspersql

    SSCrazy

    Points: 2064

    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.

  • caspersql

    SSCrazy

    Points: 2064

    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

     

     

  • ScottPletcher

    SSC Guru

    Points: 98016

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeff Moden

    SSC Guru

    Points: 993862

    caspersql wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • caspersql

    SSCrazy

    Points: 2064

    Jeff Moden wrote:

    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.

  • Jeff Moden

    SSC Guru

    Points: 993862

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • rVadim

    Hall of Fame

    Points: 3865

    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.

  • ScottPletcher

    SSC Guru

    Points: 98016

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • caspersql

    SSCrazy

    Points: 2064

    Thank you all very much indeed!

  • ScottPletcher

    SSC Guru

    Points: 98016

    FYI, just in case you care, I was able to get my code fully corrected.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeff Moden

    SSC Guru

    Points: 993862

    caspersql wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply