TSQL Beginner: Using Between or And? - Will they both include the first entry?

Transact-SQL
1

Hello,

I have a question, in regards to the following:

SELECT*
FROM [HumanResources].[Employee]
1) WHERE BirthDate >= '1/1/1970' AND BirthDate <= '1/1/1980'
2 )WHERE BirthDate BETWEEN '1/1/1970' AND '1/1/1980'

On 1) - If someone was born on the 1st Jan 1990, because I have include the >= greater than or equal to, symbol. Their record SHOULD Show. - Correct?

On 2) - If i use the between syntax, If someone was born on the 1st Jan 1990, - They should NOT appear?

For good practise is it better to use the :
WHERE BirthDate >= '1/1/1970' AND BirthDate <= '1/1/1980'

Thank you! :slight_smile:
(I haven't got around added and testing new records yet)

2

I rarely use BETWEEN and 99.x% never use it for dates. I also do not use BirthDate <= '1/1/1980' but rather "<" instead.

The problem with Dates is if they also include Time.

BirthDate BETWEEN '1/1/1970' AND '1/1/1980'

will include anyone with a Date+Time of Midnight on 01-Jan-1980, but not someone with a time of "one minute past midnight"

I've seen people trying to do

BirthDate BETWEEN '1/1/1970' AND '1/1/1980 23:59:59.999'

but that doesn't work because DateTime doesn't hold the time to exactly 3 decimal places ... and DateTime2 has lots MORE decimal places.

I suspect you actually mean "Up to but not including 01-Jan-1980", if so then this would be fine

BirthDate >= '1/1/1970' AND BirthDate < '1/1/1980'

(NOTE "<" and not "<=")

So for dates typically the best test is:

MyDateTimeColumn >= '1/1/1970' AND MyDateTimeColumn < OneUnitAfterTheEndLimit

Lastly, don't use "1/1/1980" for string dates, its ambiguous as to Day / Month. SQL parses string dates based on all sorts of things - including the Selected Language for the Currently Logged on User ... so tomorrow SQL's idea of "1/2/1980" might be 02-Jan, or might be 01-Feb.

The format '19800101" i.e. "yyyymmdd" will always be parsed UNambiguously - so use that format, anything else with day-month-year and any separators / punctuation / spaces is at the mercy of what the parser decides is the current method!

1980-02-01 is no better - try this:

SET LANGUAGE 'us_english'
PRINT 'Test US English'
GO
SELECT CONVERT(datetime, '1980-02-01')
GO
SET LANGUAGE 'french'
PRINT 'Test French'
GO
SELECT CONVERT(datetime, '1980-02-01')
GO