CODEWITHSUNDEEP
sqlsql-server
user979331
user979331

Reputation: 11911

SQL Null dates return 1900-01-01

Here is part of my query:

IsNull(CONVERT(date, V_CONSTAT_ACTUAL_DATES.ID50), '') AS 'actualFinish'

V_CONSTAT_ACTUAL_DATES.ID50 is a NULL and is a datetime column. So the results I get is 1900-01-01, what I am trying to do is return nothing just ' ' How would I accomplish this?

Upvotes: 0

Views: 5428

Answers (3)

rajquest
rajquest

Reputation: 711

Simple ISNULL Nested CAST function can remove 1900-1-1 value if data is NULL

ISNULL(CAST(CAST(<<DateColumn>> AS DATE) AS Varchar),' ') [<<Date Column Name>>]

Upvotes: 1

David McEleney
David McEleney

Reputation: 3813

Rather than ISNULL or COALESCE try using a CASE statement -

SELECT CASE WHEN date IS NULL THEN '' ELSE CONVERT(date, V_CONSTAT_ACTUAL_DATES.ID50) END

Upvotes: 1

Daniel Gimenez
Daniel Gimenez

Reputation: 20589

The mixing of types you have now is definitely a problem.

If you want to display an empty string for a null date you need convert the date into a short date time string not a date. You can use the convert function to do that. The last parameter accepts a style code so you can have it display just the date part.

COALESCE(CONVERT(varchar(8), V_CONSTAT_ACTUAL_DATES.ID50, 101), '') AS 'actualFinish'

This will display the date in mm/dd/yy format if the column is not null, or an empty string if it is.

To see more about date to string conversions go to https://msdn.microsoft.com/en-us/library/ms187928.aspx.

Upvotes: 4

Related Questions