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

Question

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

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

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

Thom A SSC Guru Points: 99137 More actions · Answer 1

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

Jeffrey Williams SSC Guru Points: 90346 More actions · Answer 2

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);

This reply was modified 2 years, 2 months ago by Jeffrey Williams.

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

Teewhy SSC Veteran Points: 203 More actions · Answer 3

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?

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

Cookies on SQLServerCentral