Using a between Date in SQL QUERY

Question

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?

Community · Accepted Answer · 2020-07-29 18:42:24Z

6

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'

edited Jul 29, 2020 at 18:42

CommunityBot

11 silver badge

answered Apr 26, 2017 at 11:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eyad Shahroury Over a year ago

but how if i want use the following format dd/mm/yyyy in my sql??

Gordon Linoff Over a year ago

@EyadShahroury . . . I strongly recommend that you use the ISO standard date formats. If you really insist on other date formats, I would suggest that you explicitly convert the constant values to dates using convert().

Eyad Shahroury Over a year ago

OK , I will :) , THANKS

Collectives™ on Stack Overflow

Using a between Date in SQL QUERY

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related