• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 981
  • Last Modified:

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

0
royalcyber
Asked:
royalcyber
  • 3
  • 2
  • 2
2 Solutions
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now