Code to sum hours and minutes (varchar), 800,000 rows.

  • Teewhy wrote:

    Secondly, the data was imported from excel in hh:mm format but it is coming up in sql as varchar.

    I have tried casting it as int in sql to get around the varchar issue but it is coming up as error.

    The time datatype is designed to represent a time of day, so if you have values equal to or greater than 24:00 it's not going to be able to store that as there is no such time.

    Traditionally, when you want to store a period of time (not a time of day), you are better off using something else. An int would work, to denote the number of ticks (minutes/seconds for example) but you can't just take your value, such as '25:37' and CAST/CONVERT it to an int; you'd need to parse that value first.

    As the value came from Excel, you might have actually been "better off" putting the data into a staging table first as a datetime, as a time with a value of > 24:00 is actually stored in excel as a date and time, and then displayed as hours. Then you could have used DATEDIFF on your datetime column in your staging table to insert a numerical value into your production table.

    What's done is done now, but something to consider next time.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • This can be done using SwePeso's method - without using PARSENAME:

    SELECT Total = CONCAT(SUM(t.sec) / 60, ':', SUM(t.sec) % 60)
    FROM (VALUES ('23:01'),('00:01'),('05:15'),('00:45'),('00:11')) AS td(Trip_Duration)
    CROSS APPLY (VALUES (CAST(LEFT(td.Trip_Duration, CHARINDEX(':', td.TripDuration, 1) AS INT) * 60
    + CAST(RIGHT(td.Trip_Duration, 2) AS INT))) AS t(sec);

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I find this pointer (loading the data in a staging table) very immensely invaluable. But how do I do that? I am working off Server management studio, I have more of such similar data from excel and I want to import them to SQL server. I have tried seeing videos to help but they don't speak to my issue. Could you please help?

Viewing 3 posts - 16 through 17 (of 17 total)

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