Find Date+Time between query in MS sql server 2008

Transact-SQL
1

Hello Sir,

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

query

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

Output-

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

Please help

2

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

3

please provide some sample data.

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

1 Like
4

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

5

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

SELECT ...
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.

6

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.

7

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'))))
8

Thank U