Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-07-27 : 10:09:31
|
| ethan writes "I'm building a system here at work that keeps track of which employees are in the building, which are out, and those whose status is unknown. There is a status field to keep track of this. Every night at 1am there is a SQL job that runs and resets the status of everyone to UNKNOWN. However, we want someone to be able to sign out for vacation, and have the system list them as OUT for the duration of the vacation and not reset them to UNKNOWN. This reset now needs to be dependent on date and time. In other words, if the return date/time of an employee is anytime after 7am of the current date (remember the job runs everyday at 1am), than they should remain signed OUT. Everyone else gets reset to UNKNOWN. I could easily do this if it was just looking at the date. But the time factor (anything before 7am gets reset) is throwing me for a loop in the SQL. We have a Return Date field which contains the employee's return date and time (ie 8/12/01 8:00:00 AM). The current SQL in the reset job is below, but this is the old one that only looked at the date. It would reset everyone to Unknown whose return date was today or any previous date (or a Null value for new entries). It now needs to do this if someone's date/time listing has a time of 7am or less. How can I represent this in SQL?? I've been stumped!UPDATE employeesSET empStatus = 'UNKNOWN'WHERE empReturnDate <= Getdate() OR empReturnDate IS NULLThanks much!" |
|
|
|
|
|
Using both date & time component to compare date to current date