Using Between for dates question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using Between for dates question

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-27 : 11:02:09

When I use the BETWEEN for a date search, it does not seem to be pulling the beginning date and end date exactly as it should. Here's an example:


DECLARE @BEGIN AS SMALLDATETIME
DECLARE @END AS SMALLDATETIME 

SET @BEGIN = '05/01/2005'
SET @END = '05/31/2005'

SELECT ID, NAME, DATE
FROM EMPLOYEE
WHERE DATE BETWEEN @BEGIN AND @END

With this simple query it will pull everything from @BEGIN and @END, but for some reason it ignores everything that is greater than 05/01/2005 and less than 05/31/2005. So it pulls everything in between those 2,but does not pull either the begin of month date or end of month date. Is there a way to set it so that it does pull everything including the beginning and end of month date?

I tried the following also, but same result:


SELECT ID, NAME, DATE
FROM EMPLOYEE
WHERE DATE >= @BEGIN AND DATE <= @END

Any ideas?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 11:18:19

If there is a time portion in the DATE column, then the @END might be in trouble.
@BEGIN, that should work in my book.

rockmoose

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-27 : 12:03:27

Here's how the record looks like:


2005-05-31 12:00:00.000

Would that 12:00:00.000 affect what is pulled? Is there a work around?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-27 : 12:07:24

Rockmoose is right. Here are two typical ways to handle it:


DECLARE @BEGIN AS SMALLDATETIME
DECLARE @END AS SMALLDATETIME 

SET @BEGIN = '05/01/2005'
SET @END = '05/31/2005'

--to use between:
--change @end to last second of the day (minute for smalldatetime)
set @end = dateadd(minute,-1,dateadd(day,1,@end))

SELECT ID, NAME, DATE
FROM EMPLOYEE
WHERE DATE BETWEEN @BEGIN AND @END

--OR 

SELECT ID, NAME, DATE
FROM EMPLOYEE
Where	[date] >= @begin 
and	[date] < dateadd(day,1,@end)

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 12:15:01

Nothing new here....

Edit: deleted

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-27 : 12:29:40

As always, make sure that your date column in your SELECT is an actual datetime datatype and not being converted to a VARCHAR for formatting purposes. (which as we all know you should never do, and this is one of the biggest reasons why)

- Jeff

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-07-27 : 15:56:05

Thanks that did the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources