[sql] check for null date in CASE statement, where have I gone wrong?

My source table looks like this

Id     StartDate
1      (null)
2      12/12/2009
3      10/10/2009

I want to create a select statement, that selects the above, but also has an additional column to display a varchar if the date is not null such as :

Id     StartDate    StartDateStatus
1      (null)       Awaiting
2      12/12/2009   Approved
3      10/10/2009   Approved

I have the following in my select, but it doesn't seem to be working. All of the statuses are set to Approved even though the dates have some nulls

        select
             id,
             StartDate,
        CASE StartDate
        WHEN null THEN 'Awaiting'
        ELSE 'Approved' END AS StartDateStatus
        FROM myTable

The results of my query look like :

Id     StartDate    StartDateStatus
1      (null)       Approved
2      12/12/2009   Approved
3      10/10/2009   Approved
4      (null)       Approved
5      (null)       Approved

StartDate is a smalldatetime, is there some exception to how this should be treated?

Thanks

This question is related to sql sql-server-2005 case smalldatetime

The answer is


select Id, StartDate,
Case IsNull (StartDate , '01/01/1800')
When '01/01/1800' then
  'Awaiting'
Else
  'Approved'
END AS StartDateStatus
From MyTable

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to case

PostgreSQL CASE ... END with multiple conditions SQL Server: use CASE with LIKE SQL Server IIF vs CASE SELECT using 'CASE' in SQL SELECT query with CASE condition and SUM() GROUP BY + CASE statement SQL use CASE statement in WHERE IN clause SQL Server - Case Statement where to place CASE WHEN column IS NULL in this query Regarding Java switch statements - using return and omitting breaks in each case

Examples related to smalldatetime

How to select data from 30 days? check for null date in CASE statement, where have I gone wrong?