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

royalcyberAsked:
Who is Participating?
 
TaconvinoCommented:
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.

TCV
0
 
TimYatesCommented:
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...
0
 
TimYatesCommented:
would = *should*

;-)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
0
 
TaconvinoCommented:
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.

+TCV
0
 
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
0
 
TaconvinoCommented:
This should do the trick:

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

+TCV
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.