Use CAST (or CONVERT) to handle Null Date values in Microsoft SQL Server

In an SQL Server View, a problem is that the DateTime field has many Null values which is causing a problem with the parsing of the data in MSAccess.

How to I use CAST (or CONVERT) to handle Null Date values in my SQL Server view?

There is a way to use CAST or CONVERT similar to the nz type function in MSAccess to handle null date values but I can’t remember the syntax.

What is happening now is that I get a data mismatch in my MSAccess function when it hits a Null Date value. Can I somehow use the ISNULL or ISDate function? I believe I need to somehow return “” instead of Null.


Returns the first nonnull expression among its arguments.

COALESCE ( expression [ ,…n ] )

Is an expression of any type.
Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible
to the same type.
Return Types

Returns the same value as expression.

If all arguments are NULL, COALESCE returns NULL.
COALESCE(expression1,…n) is equivalent to this CASE function:
WHEN (expression1 IS NOT NULL) THEN expression1 …
WHEN (expressionN IS NOT NULL) THEN expressionN


set @dateval = coalesce (InDateVal,'1900-01-01')

Leave a comment