SQL Case statement based on dates

case, sql, sql-server-2008, sql-update, t-sql

Solution

Like this:

SELECT 
  CASE 
    WHEN DATEDIFF(dd, GETDATE(), @duedate) >= 1 THEN 'blocked' 
    ELSE 'Not' 
  END AS Status;

SQL Fiddle Demo

Note that: If you didn't specify an `ELSE` clause, the default will be `NULL`.

Update: You can insert into a table with `CASE` expression like so:

INSERT INTO Statuses VALUES
(CASE 
   WHEN DATEDIFF(dd, GETDATE(), CAST('20121025' AS DATE)) >= 31 THEN 'Blocked' 
   ELSE 'Not' 
 END);

Updated SQL Fiddle Demo

Problem

I need to create a query that includes a CASE statement that basically says ``` CASE if getdate() > datedue by 31 days then status = 'Blocked END ``` Does anybody know how to check if todays date is greater than say, 25/10/2012 by 31 days? EDIT ``` Select co.OrderID, cu.FName + ' ' + cu.SName as 'Name', cu.Address1 + ', ' + cu.Address2 + ', ' + cu.Address3 as 'Dispatch Address', cu.PostCode, ma.MaterialName as 'Item', mi.Price as 'Item Price', co.DateOrdered as 'Order Date', pm.DueDate, pm.Overdue, HERE I NEED TO WRITE A CASE STATEMENT TO INSERT INTO A LOCKEDACCOUNT TABLE so for example CASE WHEN DATEDIFF(dd, GETDATE(), pm.DueDate) >= 31 THEN INSERT INTO LOCKEDACCOUNT (id, status, datelocked, customerid) VALUES (.....) END from Customers cu ```

Original source