I was using these statements
DECLARE @FirstDayofPrevMonth datetime
SET @FirstDayofPrevMonth = DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)
DECLARE @LastDayofPrevMonth datetime
SET @LastDayofPrevMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
and then using them in a where clause like so:
WHERE FNDate >= @FirstDayofPrevMonth AND FNDate <= @LastDayofPrevMonth
What would be the reason to use "Between"?
BETWEEN is effectively the same thing as >= and <=.
Note that though it is less typing it can lead to possible wrong results.
Many times you want to get results within a single date. You would write:
WHERE DateTimeField BETWEEN '20140918' AND '20140919'
In this case, lets say that there are the following entries in the table:
1 '20140918 23:59:59'
2 '20140919 00:00:00'
3 '20140919 00:00:01'
The above where statement would get entries 1 and 2. So though in most of the times you would get the correct results, it is much more safer to use the following:
WHERE DateTimeField >='20140918' AND DateTimeField < '20140919'
In the case that you are showing there is absolutely no reason to use BETWEEN: the two expressions are functionally equivalent.
A good case for BETWEEN is when the value on the left is a result of evaluating an expression, or a reference to a parameter, for example
WHERE @AsOfDate BETWEEN t.ActiveFrom AND t.ActiveTo
or
WHERE DATEADD(day, t.DaysToDeliver, t.OrderDate) BETWEEN @BlackoutBegin AND @BlackoutEnd
Here, an equivalent pair of >= and <= would require repeating part of the WHERE clause, which is not as readable as BETWEEN operator.
There is no difference in the key word between and <= or >= . They will give same result. But between is but for readable purpose and don't repeat the column name as well.
select * from table_name where column_name between '2014-01-01' and '2014-01-31';
select * from table_name where column_name>='2014-01-01' and date<='2014-01-31';
You can see it in above queries they will give same result but between is in readable format.
There's little to no difference, only that one could be argued as being easier to read.
For example, People could argue that:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
is easier to read than:
SELECT column_name(s)
FROM table_name
WHERE column_name >=value1 AND column_name <=value2;
Otherwise, there isn't exactly much difference. Except more typing, for course (thanks Jake) ;)
Some people would still disagree though that they don't like the 'BETWEEN' clause, as it can sometimes be tricky with dates/etc. However, as with all code, it was designed for a reason. And, if used properly, can be very helpful!
Some would argue day and night that they'd use the AND clause - and that could be simply down to them knowing/using it more often, and have got used to it's little 'tweaks and twerks' - and so they haven't used the BETWEEN statement as much.
However, if they'd used the BETWEEN statement first (before they knew what the AND statement was), then they'd argue differently.
but again, as with all code, use it enough and you'll learn to use it - and possibly even think 'how did i NOT use this before'.
EDIT
as obviously some people are talking about the 'does the include/exclude', I thought I might add this (copied from my comment below):
Less typing might not always be the answer for a novice, but someone who knows what they're doing, it can be quite helpful! (i'm not a pro by any means) - but BETWEEN to me would imply exclusive. But yes, i understand where people are coming from, esp examples such as pick a number between 1 and 10. The whole word in the english language has some ambiguity!!!
If you want to read more, there is this blog post talking about "Bad habits to kick : mis-handling date / range queries".
This gives a little explaination as to "Learn how to use the SQL BETWEEN condition with syntax and examples"
Personally, I would prefer to reserve use the Between statement more for say, employee ID's, where the values are ints/etc, rather than dates (this is purely preference, as both would be 'fine' to use).
As I recall, there's no difference. But see for yourself if:
SELECT * FROM `table` WHERE `date` BETWEEN 12917700 AND 12917300
and:
SELECT * FROM `table` WHERE `date` >= 12917700 AND `date` <= 12917300
produce the same results.
