March 9, 2015 at 10:44 am
I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is 335:55:20.97 duration time. My code is below.
CPUTM = 335:55:20.97 duration time
CPUZIPTM = 0:00:01.96 duration time
select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) > '02:00:00.00')
March 9, 2015 at 12:43 pm
Hi,
The time datatype isn't really for durations, it represents the time of day, so if I'm reading your data correctly it'll throw an error when converting 335 hours to a time of day. It's also not possible to add time with the '+'.
If you can't alter the database to store the values as something more helpful than varchar then
one solution might be to convert your values to seconds, add them and compare to 7200 (number of seconds in 2 hours).
March 10, 2015 at 9:17 am
SELECT *
FROM ( --[SMF_DATA].[dbo].[System_Management_Facility]
SELECT CPUTM = '335:55:20.97',
CPUZIPTM = '0:00:01.96'
) AS test_data
WHERE
(LEFT(CPUTM, CHARINDEX(':', CPUTM) - 1) >= 2) OR --check if CPUTM is already >= 2 hrs
(CAST(LEFT(CPUTM, 1) + ':00:00' AS datetime) + CAST(RIGHT(CPUTM, 8) AS datetime) + CAST(CPUZIPTM AS datetime) > '02:00:00.00')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply