Please, read my comment to the question.
SELECT <Field_list>
FROM Location
WHERE dtTime >= DATEADD(dd, 0, DATEDIFF(dd,0, dtTime)) AND dtTime <= DATEADD(ss, -1, DATEDIFF(dd,0, DATEADD(dd, 1, dtTime)))
AND DTL_Status IN('SC','NI')
Where is the trick? You have to set time between
00:00:00AM
and
11:59:59PM
of current date. It is possible by using
DATEADD[
^]and
DATEDIFF[
^] functions.
DECLARE @dtTime DATETIME = GETDATE()
SELECT @dtTime, DATEADD(dd, 0, DATEDIFF(dd,0, @dtTime)), DATEADD(ss, -1, DATEDIFF(dd,0, DATEADD(dd, 1, @dtTime)))
Returns:
2015-04-15 19:55:26.850 2015-04-15 00:00:00.000 2015-04-15 23:59:59.000
More about:
How to remove time part of datetime?[
^]