Help with BETWEEN and dates - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with BETWEEN and dates

Author

Topic

Razzle00
Starting Member

35 Posts

Posted - 2010-04-14 : 13:36:50

Hi,

I have a transaction with the date of 04/08/2010 07:30:00 AM that I am trying to pull with a SQL statement using the BETWEEN clause that is created by my application from dates entered by an end-user. Could anyone explain to me why the result of this SQL statement pulls nothing.

SELECT * FROM mytable WHERE id= '001' AND trdate BETWEEN '04/08/2010' AND '04/08/2010' ORDER BY company

The only way I can get the transaction to pull in by using this statement with the date range changed to the 9th.

SELECT * FROM mytable WHERE id= '001' AND trdate BETWEEN '04/08/2010' AND '04/09/2010' ORDER BY company

If a end-user enters a range of 04/08/2010,04/08/2010 it should pull all transactions on the 8th. If an end-user enters 04/08/2010,04/09/2010 it should pull all the transaction that happened on the 8th & 9th. But this is not the case as you can see with my example above. Any suggestions how to handle this?

Thanks,

Razzle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-14 : 13:41:25

It's because 04/08/2010 equals 04/08/2010 at 00:00. So if you want an entire day, you need to do this instead:

SELECT * FROM mytable WHERE id= '001' AND trdate >= '04/08/2010' AND trdate < '04/09/2010' ORDER BY company

So if they enter just 04/08/2010, then for the < part, put DATEADD(dd, 1, '04/08/2010')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-14 : 13:42:02

To be more clear, yours is actually saying, give me all data that is exactly equal to 04/08/2010 00:00.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Razzle00
Starting Member

35 Posts

Posted - 2010-04-14 : 14:04:45

Thanks for the explanation tkizer. I believe DATEADD(dd, 1, '04/08/2008') is the code I was looking for. Can I now work the statement this way?

SELECT * FROM mytable WHERE id= '001' AND trdate BETWEEN '04/08/2010' AND DATEADD(dd, 1, '04/08/2010') ORDER BY company

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-14 : 14:09:22

No as BETWEEN is inclusive of 04/09/2010 00:00. You must use >=, < to only get 04/08/2010 data. BETWEEN is equivalent to this: trdate >= '04/08/2010 00:00' and trdate <= '04/09/2010 00:00'. Notice the <=.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Razzle00
Starting Member

35 Posts

Posted - 2010-04-14 : 14:18:30

Ok thanks. Got it now.

Razzle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-14 : 14:23:35

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 08:39:54

Also input your dates in YYYYMMDD format so that it works for all datesettings

Madhivanan

Failing to plan is Planning to fail