CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetimebetween
Eyad Shahroury
Eyad Shahroury

Reputation: 1

Using a between Date in SQL QUERY

Im using vb.net code with Sql Server 2008 R2.
I'm trying to get result by using a SQL query to get all rows that have a value between 2 dates: Here is my the where clause of my statement:

Where (CONVERT(varchar(10), visit_nextVisitDate, 103) between '02/04/2017' AND  '15/05/2017')"

but I always get all rows for the same month (month 4). I tried this:

WHERE (CAST(dbo.Visits.visit_date AS date) BETWEEN '24/04/2017' AND '02/05/2017')

but I got an error cause my date fields are saved in format yyyy/mm/dd

How can I change the SQL date format to dd/mm/yyyy?

Upvotes: 0

Views: 26281

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Why would you do date comparisons using strings? That is just wrong, wrong, wrong. (If you do it, use ANSI standard formats, YYYY-MM-DD so the comparisons are correct.)

Just do this using dates:

Where visit_nextVisitDate between '2017-04-02' AND '2017-05-02'

Actually, it is a bad idea to use between with dates. Aaron Bertrand has a very good blog on this subject.

I recommend:

Where visit_nextVisitDate >= '2017-04-02' AND 
      visit_nextVisitDate < '2017-05-03'

Upvotes: 6

Related Questions