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!
EZTEKAsked:
Who is Participating?
 
LowfatspreadCommented:
DELETE

 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....
0
 
Om PrakashCommented:
DELETE t FROM table1 t
inner join table2 t2 on t1.userid = t.userid and LastRosterChangeDate <= dateadd(hour,3, getDate())
0
 
conrabaCommented:
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.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
LowfatspreadCommented:
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
0
 
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.
0
 
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)
0
 
EZTEKAuthor Commented:
no one has a clue? :(
0
 
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.
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.