SQL: conditional statements in select queries using case
When you need conditional statements in an SQL query, for instance to do some date calculation within the SELECT statement, you can do:
SELECT DISTINCT employeeid,
CASE
WHEN ISDATE(t1.date_end) = 1
THEN CONVERT(datetime, t1.date_end)
ELSE getdate() + 1000
END AS date_end
FROM temployee t1
The above statement returns all employeeid’s with the date_end record. The date_end record however can be empty but not NULL (otherwise an ISNULL would be easier). So we need a CASE statement to see if the date_end field contains a date value. If not the current date + 1000 days is returned.