SQLTeam.com | Weblogs | Forums

Find Date+Time between query in MS sql server 2008


Hello Sir,

I have table "ABC" where column as dtDate in MS SQL server 2008


select sum(iLPair) as iActualPair from ABC where aid='1001016' and
(dtDate+dtTime) between '08/12/2018 00:00:01 PM' and '09/03/2018 5:59:59 PM'
having sum(iLPair)>0


Msg 8117, Level 16, State 1, Line 1
Operand data type date is invalid for add operator.

Please help


You can't just add Date and Time types in SQL Server, you have to use the DateAdd function.


please provide some sample data.

what is the column data type for both dtDate and dtTime?


In addition to the message from @yosiasz it would be beneficial to know, which index(es) you have on the table (for optimisation purpose).


You should change your query so it doesn't combine the date and time columns - it is unnecessary:

FROM ...
WHERE aid = '1001016'
AND dtDate BETWEEN '2018-08-12' AND '2018-09-03'
AND dtTime < '18:00'
HAVING sum(iLPair) > 0

This assumes the column dtDate has a data type of date and the column dtTime has a data type of time. If so - then you want to make sure you are using a string literal for the dates that is unambiguous - either YYYYMMDD or YYYY-MM-DD which are ANSI standard formats. Note: some will state that YYYY-MM-DD can be interpreted incorrectly - but that is only if using it for a datetime data type - if using for date or datetime2 data types it will be interpreted correctly.

In your current construct - a date and time value of '2018-08-12 00:00:00.000' will be excluded, as well as '2018-09-03 17:59:59.003' through '2018-09-03 17:59:59.997' (or more if using a higher precision datetime2 data type).

Also - by combining the columns you eliminate the possibility of utilizing any indexes on the date or time columns (if they exist).

And finally - there is no such time as 00:00:01 PM. There is either 00:00:01 AM or 12:00:01 PM - and if you are utilizing a time data type it is much better to utilize 24-hour time which is not dependent on a regional setting for the am/pm identifier. If you really want data from the time period between noon and 6pm - I have to assume that is for every day - which would change your query to:

AND dtTime >= '12:00'
AND dtTime < '18:00'

That would return values between 12 and 6pm for all dates in the specified date range. I am assuming that isn't what you really want - and that you want everything between the start date and the end date up to - but not including the end time.


That's not the same thing. For example 2018-08-14 19:00 would be included in the original query, but not in your separated one. Honestly if you are going to treat a date + time as a combined entity then you should store them both in a single DateTime column. But if you can't then you have to combine them before trying to compare them to a range of date/times.


For best performance, you need to query each column separately, something like this:

select sum(iLPair) as iActualPair from ABC where aid='1001016' and
((dtDate >= '20180813' AND dtDate <= '20180902') OR 
 (((dtDate = '20180812' AND dtTime >= '00:00:01 PM') OR 
   (dtDate = '20180903' AND dtTime <= '5:59:59 PM'))))


Thank U