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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply