01/01/1900 as NULL

Question

User496086301 posted

ISNULL(Format(YP.TCCP_DATE,'MM/dd/yyyy'),'') as Field_Value6,

I am already using the above but needs to add reading 01/01/1900 as NULL.

How can I add that?

Answer 1

User753101303 posted

Hi,

If SQL Server 2012 or later you could try :

DECLARE @Test TABLE(Value DATE)
INSERT INTO @Test VALUES ('')
SELECT ISNULL(Format(IIF(Value='19000101',NULL,Value),'MM/dd/yyyy'),'') FROM @Test

Usually it happens when mistaknely trying to insert an empty string so I would fix this so that those 1900-01-01 dates are null as well. Also my personal preference is to format data on the "client" side. For example here you force this format while by getting a real date out of SQL Server you can still easily test this value, compute Something and possibly support whatever language you want....

Answer 2

User475983607 posted

ISNULL(Format(YP.TCCP_DATE,'MM/dd/yyyy'),'') as Field_Value6,

I am already using the above but needs to add reading 01/01/1900 as NULL.

How can I add that?

Use COALESCE.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017