# Find Difference In Time

I am attempting to write a stored procedure to determine the difference in seconds between two times. If I were to hardcode the values I know I can use

``````CAST(datediff(second, [start], [end])/3600.0 As Decimal(18,4)))
``````

but I am needing to only get the difference if a condition is met. My thought is to do something like this

``````Declare @diff decimal(18,4)
Create Table #Dope
(
date1 datetime
,inlate decimal(16,4)
)

INSERT INTO #Dope (date1) Values
('2016-01-04 08:08:47.000'),
('2016-01-04 08:25:59.000'),
('2016-01-04 08:27:47.000'),
('2016-01-04 08:00:00.000')

if CAST(date1 As Time) > '08:00:00.000'
--Here is where the capture would come but I am unsure of how to type it
SET @diff = CAST(datediff(second, [], [date1])/3600.0 As Decimal(18,4))
``````

How do I set the 1st datetime in my Set statement to be 8 a.m. of the day that I am needing the difference in s econds? Meaning, I can't just hardcode '08:00:00.000' into my Set statement as the two dates my be different which would give me seconds spanning days and that is not what I am after. I just want to see the difference in seconds between date1 and '08:00:00.000'

First of all I'm not sure why you are dividing by 3600 .. that would return you 1/3600th of a second.

Second you are over thinking things a bit. Try this:

``````CREATE TABLE #NotDope
(
date1 datetime
)

INSERT INTO #NotDope (date1) Values
('2016-01-04 08:08:47.000'),
('2016-01-04 08:25:59.000'),
('2016-01-04 08:27:47.000'),
('2016-01-04 08:00:00.000'),
('2016-01-04 07:55:00.000')

SELECT date1,
CASE WHEN CAST(date1 AS Time) > '08:00:00.000' THEN
datediff(second, '08:00:00', CAST([date1] AS time))
ELSE 0 END AS SecondsLate
FROM #NotDope
``````

All you need to do is cast your date as a time data type to get rid of the date part.