CODEWITHSUNDEEP
sql-servert-sql
dcraven
dcraven

Reputation: 139

Simple select query not working with 'AND' operator

enter image description here

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

Upvotes: 1

Views: 187

Answers (5)

user6691848
user6691848

Reputation: 

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

Upvotes: 1

HappyTown
HappyTown

Reputation: 6514

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 )

Upvotes: 4

Mouaici_Med
Mouaici_Med

Reputation: 410

you can declare StartTime and EndTime and your request be:

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

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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"

Upvotes: 1

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

Try without the single quotes:

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

Upvotes: 2

Related Questions