SQL Server Delete Query based on two dates

Hi everyone.
I have a SQL question:

Does anyone know how to do this:

DELETE FROM table1 USERID, FNAME, LNAME, table2.TEAMID WHERE table1.USERID=table2.USERID AND LastRosterChangeDate < todaysdate.

THe kicker is that the application is based in EASTERN timezone, and the server located in CENTRAL. SO i have time offset I have to deal with as well.

THe application is a sports team database, and the players can remove themselves from a team at anytime, however I only want them to remove themselves from the eventDate IF todays date has not passed the LastRosterChangeDate (and take into account the 3 hour time difference in my server)

Hope that wasnt too confusing!
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.

Om PrakashCommented:
DELETE t FROM table1 t
inner join table2 t2 on t1.userid = t.userid and LastRosterChangeDate <= dateadd(hour,3, getDate())
You need to determine what timezone LastRosterChangeDate  is being saved as (Application Server Time, Database Server Time or plain UTC time).  Then you can agree on the time difference  againts your BASE time zone.  There is a need to be careful since there are unanswered parameters here.
yes why should the time bother you?

the db server time should be used and all roster times etc recorded for that ....

display /event time /location time should then be based upon that
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

EZTEKAuthor Commented:
well because the application itself is running in Eastern Time zone.
All of the teams and players are based out of Indiana, Michigan, and Ohio.
The SQL Server is in a different time zone.

The asp.net application runs the insert and delete queries, and trust me (Ive verified) that the date needs to be modified to fit the eastern zone.
EZTEKAuthor Commented:
OOPS! There are two parameters that were forgotton.

DELETE FROM table1 USERID, FNAME, LNAME, table2.TEAMID WHERE table1.USERID=table2.USERID AND table3.LastRosterChangeDate < todaysdate.

The first two tables (table1 and table2) are linked by TEAMID
table3 is where the LastRosterChangeDate is at, and I pass @EventID to the query so it knows what Event this player must be deleted for.

Hope this makes sense!

Common Links:
table1 <==> table2    |   TeamID is the primary Key
table3 is where the LastRosterChangeDate is at (EventID is primary key)
EZTEKAuthor Commented:
no one has a clue? :(

 FROM teamforevent
where player='x'
   and team='y'
   and eventid=@eventid
  and exists (select lastrosterchangedate from events
    where eventid=@eventid
     and LastRosterChangeDate <= dateadd(hour,3, getDate())

if you can use the system date on the database server
or pass the time from the application server in place of getdate()
assuminf that 3 hours is the adjustment required?

if that doesn't work...

specify the table and column names involved....

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
EZTEKAuthor Commented:
so, if reading this correctly, I need to pretty much check EACH eventID to run this statement. Which is perfect anyway, as it adds an additional control.
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.