March 6, 2023 at 9:02 am
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
March 6, 2023 at 10:59 pm
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
May 8, 2023 at 1:19 am
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