Replace NULL value in datetime column to an empty string – SQL Server

We were stuck for a moment today in class when trying to replace the NULL values in a date column with an empty string since the ISNULL and COALESCE methods return the default date 1900-01-01.

Here are the initial queries.

SELECT C.iCustomerID,
vcCustomerName,
ISNULL(OH.iOrderID,0) OrderID,
COALESCE(OrderedDate, NULL, ”) OrderedDate,
COALESCE(vcDeliveryStatus, NULL, ‘N/A’) AS DeliveryStatus
FROM tbCustomer C
LEFT JOIN tbOrdersHeader OH ON C.iCustomerID=OH.iCustomerID
LEFT JOIN tbOrdersDetail OD ON OH.iOrderID=OD.iOrderID
LEFT JOIN stbDeliveryStatus DS ON OD.iDeliveryStatusID=DS.iDeliveryStatusID
ORDER BY C.iCustomerID

SELECT C.iCustomerID,
vcCustomerName,
ISNULL(OH.iOrderID,0) OrderID,
ISNULL(OrderedDate, ”) OrderedDate,
COALESCE(vcDeliveryStatus, NULL, ‘N/A’) AS DeliveryStatus
FROM tbCustomer C
LEFT JOIN tbOrdersHeader OH ON C.iCustomerID=OH.iCustomerID
LEFT JOIN tbOrdersDetail OD ON OH.iOrderID=OD.iOrderID
LEFT JOIN stbDeliveryStatus DS ON OD.iDeliveryStatusID=DS.iDeliveryStatusID
ORDER BY C.iCustomerID

Both queries return 1900-01-01, which is the standard SQL Server date. After some time, we learned that a datetime value cannot store a string. It is impossible for a row to have anything other than a valid datetime value or NULL at the same time.

So we changed the query to below, to get the desired output.

SELECT
C.iCustomerID,
vcCustomerName,
ISNULL(OH.iOrderID,0) OrderID,
COALESCE( CONVERT(VARCHAR(10), OrderedDate, 120), NULL, ‘ ‘) OrderedDate, COALESCE(vcDeliveryStatus, NULL, ‘N/A’) AS DeliveryStatus
FROM tbCustomer C
LEFT JOIN tbOrdersHeader OH ON C.iCustomerID=OH.iCustomerID
LEFT JOIN tbOrdersDetail OD ON OH.iOrderID=OD.iOrderID
LEFT JOIN stbDeliveryStatus DS ON OD.iDeliveryStatusID=DS.iDeliveryStatusID
ORDER BY C.iCustomerID