CODEWITHSUNDEEP
sqlsql-servert-sql
Sohail xIN3N
Sohail xIN3N

Reputation: 3031

How to use SQL BETWEEN Clause and GETDATE function

SELECT j.JobNo, j.JobDescription
FROM Job j  
JOIN Job_Nav n ON j.JobNo = n.JobNo
WHERE n.Blocked = 0 
AND n.[Starting Date] < GETDATE() AND n.[Ending Date] > GETDATE()

I want to change AND n.[Starting Date] < GETDATE() AND n.[Ending Date] > GETDATE() into BETWEEN clause, can anyone tell me how to use Between Clause for above expression. THANKS

Upvotes: 0

Views: 14049

Answers (5)

paparazzo
paparazzo

Reputation: 45096

You can't as between is inclusive (>= <=)

BETWEEN (Transact-SQL)

To specify an exclusive range, use the greater than (>) and less than operators (<).

If you need exclusive what you have is clean
I think separate rows is easier to read 

AND n.[Starting Date] < GETDATE() 
AND n.[Ending Date]   > GETDATE()

Upvotes: 1

Chet
Chet

Reputation: 11

Not sure if this matters to you, but GETDATE() could potentially return a misleading result since the function includes the time.

Upvotes: 1

Jerrad
Jerrad

Reputation: 5290

getdate() between n.[Starting Date] AND n.[Ending Date]

is the equivalent of

getdate() >= n.[Starting Date] and getdate() <= n.[Ending Date]

(note that it is using >= and <= instead of > and <)

I assume you want to use between to make your code a little more compact and readable, but your code as written isn't a candidate for such a substitution.

Upvotes: 1

RosSQL
RosSQL

Reputation: 323

SELECT j.JobNo, j.JobDescription
FROM Job j  
JOIN Job_Nav n ON j.JobNo = n.JobNo
WHERE n.Blocked = 0 
AND getdate() between n.[Starting Date] AND n.[Ending Date]

Upvotes: 1

Ashutosh Arya
Ashutosh Arya

Reputation: 1168

SELECT j.JobNo, j.JobDescription FROM Job j
JOIN Job_Nav n ON j.JobNo = n.JobNo WHERE n.Blocked = 0 AND GETDATE() Between n.[Starting Date] AND n.[Ending Date]

Upvotes: 1

Related Questions