Simple select query not working with 'AND' operator

Question

I am working on a timetable and so I want to run a query which performs a check in the database to see all classes between a certain StartTime and EndTime. The user will select a start and end time for a class which will be stored in a label as a DateTime format 02/03/2017 00:00:00.

I want to run a query to check for a class so I want to use the selected start time to see if class is greater or equal to this start date but less than the next day 03/03/2017 00:00:00. The below query works fine but I will be using parameterised values.

My current query is:

SELECT * FROM Class WHERE (StartTime >='02/03/2017 00:00:00') AND ( EndTime <= '03/03/2017 00:00:00' )

My desired query with parameters:

SELECT * FROM Class WHERE (StartTime >='@StartTime') AND ( EndTime <= '@EndTime' )

Am I understanding correctly? "My current query" work, but the query with parameters does not? — John C, Mar 03 '17 at 21:48
I want it to work with parameterised values. I want to use the start time for both parts but I just want to increment the value of the start time by 1 day so it meets the range of my query. Ive tried this below on that link but it doesn't work http://stackoverflow.com/questions/25564482/how-to-compare-datetime-with-only-date-in-sql-server#_=_ — dcraven, Mar 03 '17 at 21:49
SELECT * FROM Class WHERE (StartTime >=@StartTime) AND ( EndTime <= @EndTime ) and declare StartTime and EndTime ? — Mouaici_Med, Mar 03 '17 at 21:50
You need to fix your date strings. They will not always be interpreted the way you want them too. It should be yyyy-mm-dd. That is the ANSI standard order regardless of language. It is the only format that will always work. As posted it is impossible to know if this is February 3 or March 2. — Sean Lange, Mar 03 '17 at 21:51
[#BackToBasics : Dating Responsibly - Aaron Bertrand](https://blogs.sentryone.com/aaronbertrand/backtobasics-dating-responsibly/) and [Bad habits to kick : mis-handling date / range queries - Aaron Bertrand](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) — SqlZim, Mar 03 '17 at 21:52
This is a two way street. There are several people here offering their time to help you figure out the problem. You should at the very least let us know what worked. You were provided a number of possibilities. — Sean Lange, Mar 03 '17 at 22:05
Of course I agree. I repsonded to the three others including yourself that it worked :) — dcraven, Mar 03 '17 at 22:08

HappyTown · Answer 1 · 2017-03-03T21:57:18.540

You have quotes around the @StartTime and @EndTime. Remove those, so something like below should give you the correct results.

declare
    @StartTime datetime = '2017-03-02 00:00:00.000',
    @EndTime datetime =  '2017-03-03 00:00:00.000'
SELECT * FROM Class WHERE (StartTime >=@StartTime) AND ( EndTime <= @EndTime )

Also to address your new requirement of

I just want to increment the value of the start time by 1 day

here is the code

declare
    @StartTime datetime = '2017-03-02 00:00:00.000'

-- Add 1 day to start time to get the end date.
declare
    @EndTime datetime =  dateadd(day, 1,  @StartTime)

SELECT * FROM Class WHERE (StartTime >=@StartTime) AND ( EndTime <= @EndTime )

score 2 · Answer 2 · answered Mar 03 '17 at 21:49

2

Try without the single quotes:

SELECT * FROM Class WHERE (StartTime >=@StartTime) AND ( EndTime <= @EndTime )

answered Mar 03 '17 at 21:49

Giorgos Altanis

2,742
1
13
14

score 1 · Answer 3 · answered Mar 03 '17 at 21:49

1

You must remove the quotes

SELECT * FROM Class WHERE (StartTime >=@StartTime) AND ( EndTime <= @EndTime )

Your query tries to parse a date out of the string "@EndTime"

answered Mar 03 '17 at 21:49

Shnugo

66,100
9
53
114

score 1 · Accepted Answer · answered Mar 03 '17 at 21:52

1

you can declare StartTime and EndTime and your request be:

SELECT * FROM Class WHERE (StartTime >=@StartTime) AND ( EndTime <= @EndTime )

answered Mar 03 '17 at 21:52

Mouaici_Med

390
2
19

score 1 · Answer 5 · 2017-03-03T22:00:16.540

1

declare @startTime datetime;
declare @endTime datetime;

set @startTime = cast(getdate() as date);  -- sets start time to start of current day
set @endTime = dateadd(day,1,@startTime);  -- sets end date to one day past start date

SELECT * FROM Class WHERE (StartTime >=@StartTime) AND ( EndTime < @EndTime ) -- use < for value to be less than next day

edited Mar 03 '17 at 22:00

answered Mar 03 '17 at 21:53

Simple select query not working with 'AND' operator

5 Answers5