CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Gilad Bronshtein
Gilad Bronshtein

Reputation: 3

SQL Server query for changing status according to date

I have one table that contains StartDate and EndDate (Datetime)

I'm looking for a nice query in SQL Server 2008R2 that will check if StartDate and EndDate are equal and if they are, change another value to '0'

In short, my program has "Learning Environments," each with a status code (open/closed/archived). At night I'm running a few scheduled procedures and if I set the opening and ending date to the same date, the status should change to be closed.

So the query should:

  1. Look for Learning Environments with same date;
  2. Check if the date is today (the process runs every night) => Today = StartDate = EndDate;
  3. Change other column (status) value from 1 to 0.

Upvotes: 0

Views: 2427

Answers (2)

Dodecapus
Dodecapus

Reputation: 391

I like using DateAdd and DateDiff since it's always been a little more efficient than other techniques.

Working sqlfiddle: http://sqlfiddle.com/#!6/f1a76/1

UPDATE Table1
SET Status = 0
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, [StartDate])) = DATEADD(dd, 0, DATEDIFF(dd, 0, [EndDate]))
  AND DATEADD(dd, 0, DATEDIFF(dd, 0, [StartDate])) = DATEADD(dd, 0, DATEDIFF(dd, 0, GetDate()))

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

Sounds like a simple UPDATE statement:

UPDATE table
SET status = 0
WHERE CAST(StartDate AS DATE) = CAST(EndDate AS DATE)
     AND CAST(StartDate AS DATE) = CAST(GETDATE() AS DATE)

Upvotes: 1

Related Questions