comparing time

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 FranWHERE
(LastUpdated >= StartTime AND LastUpdated < EndTime)
...
;
?
"Mary Fran" <MaryFran@.discussions.microsoft.com> wrote in message
news:1169DCB8-7C42-4156-8F27-9733F65F846E@.microsoft.com...
> 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|||> 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)
)
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Mary Fran" <MaryFran@.discussions.microsoft.com> wrote in message
news:1169DCB8-7C42-4156-8F27-9733F65F846E@.microsoft.com...
> 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|||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;