Sql-server – Find Difference In Time

datetimesql-server-2008t-sql

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'

Best Answer

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.

Related Question