comparing time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mary Fran - 26 Mar 2008 18:17 GMT

How do I determine if the time value of a datetime field (in SQL) is between
2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
AM# and the comparison fields are starttime = #05:00:00 PM# and
endtime=#02:00:00 AM#)?
Thanks,
Mary Fran

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 26 Mar 2008 19:19 GMT

WHERE
(LastUpdated >= StartTime AND LastUpdated < EndTime)
...
;

?

> How do I determine if the time value of a datetime field (in SQL) is
> between
[quoted text clipped - 3 lines]
> Thanks,
> Mary Fran

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 27 Mar 2008 13:52 GMT

Sorry, after reading Dan's response, I realize I misunderstood the question.
A slightly different approach (which also accounts for the case where start
and end time are the same, and lastupdated falls on that instant):

CREATE TABLE #foo
(
i INT,
LastUpdated DATETIME,
StartTime SMALLDATETIME,
EndTime SMALLDATETIME
);
SET NOCOUNT ON;
INSERT #foo SELECT 1,'2007-05-29 01:04:32', '05:00 PM', '02:00 AM'; --
should match
INSERT #foo SELECT 2,'2007-05-29 12:04:32', '05:00 PM', '02:00 AM';
INSERT #foo SELECT 3,'2007-05-29 16:04:32', '05:00 PM', '02:00 AM';
INSERT #foo SELECT 4,'2007-05-29 19:04:32', '05:00 PM', '02:00 AM'; --
should match
INSERT #foo SELECT 5,'2007-05-29 16:04:32', '05:00 PM', '11:00 PM';
INSERT #foo SELECT 6,'2007-05-29 16:04:32', '03:00 PM', '07:00 PM'; --
should match

SELECT i,LastUpdated
FROM (
SELECT i,LastUpdated,
delta = DATEDIFF(MINUTE,0,DATEADD(DAY,-DATEDIFF(DAY,0,LastUpdated),
LastUpdated)),
s = DATEDIFF(MINUTE,0,StartTime),
e = DATEDIFF(MINUTE,0,EndTime),
r = ABS(DATEDIFF(MINUTE,StartTime,EndTime))
FROM #foo
) x
WHERE (delta BETWEEN s AND s+r)
OR delta = CASE WHEN s = e THEN delta ELSE -1 END
OR delta <= CASE WHEN e < s THEN e ELSE -1 END;

DROP TABLE #foo;

Reply to this Message

Dan Guzman - 27 Mar 2008 02:16 GMT

> How do I determine if the time value of a datetime field (in SQL) is
> between
> 2 other time fields (e.g., I have a LastUpdated field =#5/29/2007 12:04:32
> AM# and the comparison fields are starttime = #05:00:00 PM# and
> endtime=#02:00:00 AM#)?

If I understand correctly, you want to ignore the date part of the database
column. Furthermore, when the specified start time is greater than the end
time, you want to reverse the criteria.

There may be more elegant methods but I believe the example below will
accomplish the task. Be advised that this technique will require a scan
because of the non-sargable expression. A more efficient approach is to
store time with a base date like January 1, 1900 to facilitate searches on
time only.

SELECT LastUpdated
FROM dbo.MyTable
WHERE
(@TimeOnlyStart < @TimeOnlyEnd AND
DATEADD(day, DATEDIFF(day, '19000101', LastUpdated)*-1, LastUpdated)
BETWEEN @TimeOnlyStart AND @TimeOnlyEnd)
OR
(@TimeOnlyStart >= @TimeOnlyEnd AND
(DATEADD(day, DATEDIFF(day, '19000101', LastUpdated)*-1,
LastUpdated) > @TimeOnlyStart
OR DATEADD(day, DATEDIFF(day, '19000101', LastUpdated)*-1,
LastUpdated) < @TimeOnlyEnd)
)

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> How do I determine if the time value of a datetime field (in SQL) is
> between
[quoted text clipped - 3 lines]
> Thanks,
> Mary Fran

Reply to this Message