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.



COALESCE

Returns the first nonnull expression among its arguments.
Syntax

COALESCE ( expression [ ,…n ] )
Arguments

expression
Is an expression of any type.
n
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.
Remarks

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

Example:

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

Leave a Reply

Your email address will not be published. Required fields are marked *