Here's a simple example of a SQL CASE statement for Microsoft SQL Server:

SELECT Day = CASE 
  WHEN (DateAdded IS NULL) THEN 'Unknown'
  WHEN (DateDiff(day, DateAdded, getdate()) = 0) THEN 'Today'
  WHEN (DateDiff(day, DateAdded, getdate()) = 1) THEN 'Yesterday'
  WHEN (DateDiff(day, DateAdded, getdate()) = -1) THEN 'Tomorrow'
  ELSE DATENAME(dw , DateAdded) 
END
FROM Table

In this CASE statement example we are switching on a column called DateAdded which is a date time field. Using various date functions we are creating a column called Day with values Today, Yesterday, Tomorrow, or the day of the week (eg Sunday).

You can also add a WHERE clause, and select other columns from the query

If you found this handy you might also like my SQL Server Cheat Sheet, enjoy.