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:

  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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.