BETWEEN statement error in query

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

SQL Server Development (2000)

BETWEEN statement error in query

Author

Topic

wnymenu
Starting Member

3 Posts

Posted - 2006-03-06 : 11:06:34

So I am using the between statement in following query, but I need to return today's date as well as all dates between today and 14 days from now. here is query:

SELECT id,eventTitle, eventDesc, eventstDate FROM tblEvent WHERE (eventstDate BETWEEN GETDATE() AND DATEADD(day, 14, GETDATE()) and restid=186

any help is greatly appreciated

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-03-06 : 11:45:21

What error are you getting? The only thing I see wrong is that you're missing a closing parenthesis.

~Travis

wnymenu
Starting Member

3 Posts

Posted - 2006-03-06 : 11:47:36

I am not getting an error per say, what I meant to say was that events that occur TODAY are not showing up, but rather the events between today and day 14 show up! I need to have today's event show up.

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-03-06 : 12:00:56

Well the problem is that getdate() will return something in the form of: yyyy-mm-dd hh:mm:ss. So your query is saying give me everything that happens from March, 06 2006 at 11:00AM until March 20, 2006 at 11:00AM. You need to strip the time off the result of getdate() for your query to return the right results.

See:
http://www.sqlteam.com/item.asp?ItemID=240
for more information on formatting your date correctly.

~Travis

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-06 : 12:05:39

This code should do it.


SELECT
	id,
	eventTitle,
	eventDesc,
	eventstDate
FROM
	tblEvent
WHERE
	-- Greater than or equal midnight (00:00:00.000) today
	eventstDate >= dateadd(dd,datediff(dd,0,GETDATE()),0)	AND
	-- Before 00:00:00.000 on the 15th day after today
	eventstDate <  dateadd(dd,datediff(dd,0,GETDATE())+15,0)

CODO ERGO SUM

wnymenu
Starting Member

3 Posts

Posted - 2006-03-06 : 12:23:54

Thanks, that worked!

www.thedakotagrill.com/events.asp, it is the first event in the list (it says 03/04, but thats the title, not the date) i changed the date to today to test, and it works by stripping out the time!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources