CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-updatecase
Annoscia
Annoscia

Reputation: 117

SQL Case statement based on dates

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

Upvotes: 4

Views: 23936

Answers (2)

AnandPhadke
AnandPhadke

Reputation: 13506

select CASE 
   WHEN DATEDIFF(dd,@duedate,getdate()) >= 31 then 'Blocked' else 'NO' end

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79959

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

Upvotes: 6

Related Questions