Select where current date is less that end date

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

General SQL Server Forums

New to SQL Server Programming

Select where current date is less that end date

Author

Topic

Coolpics22
Starting Member

5 Posts

Posted - 2009-11-03 : 14:09:47

I have an column in my table called EndDate and I need only the data with an end date after the current date to be displayed. How can this be done?
Another part to that is I would like the data to only be displayed before a certain time of day, say 5.00pm.
Does anyone have any ideas??
Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-03 : 14:28:27

So does the endDate have to before 5PM? Or do you want to display only if the current time is before 5 PM?

WHERE endDate > getdate() and datepart(hour,getdate()) < 17

Jim

Everyday I learn something that somebody else already knew

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 14:31:59

[ sniped]


CREATE TABLE x (Col1 int IDENTITY(1,1) PRIMARY KEY, EndDate datetime)
GO

CREATE INDEX xEndDate ON x (EndDate)
GO

INSERT INTO x (EndDate)
SELECT '1/1/2010 4:00PM' UNION ALL
SELECT '2/1/2010 9:00PM' UNION ALL
SELECT '3/1/2009 4:00PM' UNION ALL
SELECT '4/1/2009 4:00PM' UNION ALL
SELECT '5/1/2009 4:00PM' UNION ALL
SELECT '6/1/2009 4:00PM' UNION ALL
SELECT '7/1/2009 4:00PM' UNION ALL
SELECT '8/1/2009 4:00PM' UNION ALL
SELECT '9/1/2009 4:00PM' UNION ALL
SELECT '10/1/2009 4:00PM' UNION ALL
SELECT '11/1/2009 4:00PM' UNION ALL
SELECT '12/1/2009 4:00PM' 
GO

SELECT *, DATEPART(hh,EndDate)
  FROM x
WHERE EndDate > GetDate()
  AND DATEPART(hh,EndDate) < 17
GO

DROP TABLE x
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 14:34:50

quote:
Originally posted by jimf

So does the endDate have to before 5PM? Or do you want to display only if the current time is before 5 PM?

WHERE endDate > getdate() and datepart(hour,getdate()) < 17

Jim

Everyday I learn something that somebody else already knew

I think the row's time has to be lees than 5:00PM, not the current datetime

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Coolpics22
Starting Member

5 Posts

Posted - 2009-11-03 : 14:47:05

Wow, thanks for the quick response!

The data in my endDate column is formatted something like this, 11/02/2009 12:00:00 AM; automatically putting in the 12.00.00 AM for time. Is this formatted right? Because I have tried using getdate() but for some reason I am not getting the results I need.

The endDate wouldnt need to be before 5pm but I think that is how I would want it instead displaying it only if current time is before 5.

Thanks so far!

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 15:05:26

my guess is that your EndDate column is varchar

Is that correct?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Coolpics22
Starting Member

5 Posts

Posted - 2009-11-03 : 15:35:06

Actually my endDate column is datetime, I just had an asp calendarextendar that was putting my selected end date as 11/02/2009 12:00:00 AM instad of how I have it now as 02/11/2009 12:00:00 AM. But after making that change it works.

If I wanted to get only the data with an end date after AND on the current date should it not work to use

WHERE EndDate >= GetDate()
AND DATEPART(hh,EndDate) < 17

sorry for not specifying this in initial post.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 15:47:58

Well that would only be a problem for queries that were run AFTER 5:00PM

So you're saying that day should still Count, up until Midnight?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 16:00:49

How about



SELECT *, DATEPART(hh,EndDate)
  FROM x
WHERE EndDate > DATEADD(Day, DATEDIFF(Day, 0, GetDate())+1, 0)
  AND DATEPART(hh,EndDate) < 17
GO

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Coolpics22
Starting Member

5 Posts

Posted - 2009-11-03 : 16:02:46

I would like the day to count up to 5. but if it counts up until midnight then that is ok.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 16:06:57

no it will take all rows from midnight up until 5:00

Did you run the sample code I posted?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Coolpics22
Starting Member

5 Posts

Posted - 2009-11-03 : 18:16:07

Thanks alot for all the help!
It is working. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources