how to compare system date to date/time field in SQL database

I have an event/Calendar database table where user store information about an event such as event location, date/time etc. I would like to also allow the user to show this event within a time period such that they are able to choose "start" and "End" date to show the event.

In order for this to work I would need to compare the system date with the "end" date. If the system date is greater than the End date than it shouldn't show the event otherwise show it.

Please if someone can help me on this

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SELECT * FROM TABLE WHERE eventDate >= to_date('2003/07/09', 'yyyy/mm/dd') AND eventDate <= to_date('2004/07/09', 'yyyy/mm/dd')

Assuming '2003/07/09' and '2004/07/09' are your start and end dates (respectively)

That would work with Oracle...
would = *should*

Translation to T-SQL (SQL Server):

SELECT * FROM TABLE WHERE eventDate >= convert(datetime, '20030709', 112) AND eventDate <= convert(datetime, '20040709', 112)

Credit goes to Tim for this.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

royalcyberAuthor Commented:
Thank you guys, but I actually need to compare the sytem date(which is the server date) with the end date.

would this work

SELECT * FROM TABLE WHERE getDate() <= convert(datetime, '20040709', 112)

Now my question is that do we hav to convert these dates ato a specific format in order to compare then or this should work

Thanks again
That should work OK.  Be careful with times.  Keep in mind that:

2006-01-01 00:00:01 <= 2006-01-01 00:00:00   false
2006-01-01 00:00:00 <= 2006-01-01 00:00:00   true

If you only need dates to be compared, try this:

SELECT * FROM TABLE WHERE convert(datetime, getDate(), 112) <= convert(datetime, '20040709', 112)

You can try different combinations on SQL Server Query Analyzer and see what fits your needs.

royalcyberAuthor Commented:
Thank you very much, it works great

But I got into another problem. Currently I have a 2 level management system, what I need in the stored procedure is that if a customer signs in
then it does this

SELECT * FROM TABLE WHERE convert(datetime, getDate(), 112) <= convert(datetime, '20040709', 112)

but if an admin signs in then it should show everything

Please if you can help me in how to write an if statement. I wll pass a flag field to to the stored procedure
if it is -1 than it is a member and if it is 0 than it's  an admin
This should do the trick:

IF @YourParameter = -1
     SELECT * FROM TABLE WHERE convert(datetime, getDate(), 112) <= convert(datetime, '20040709', 112)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.