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....
Member
29 Points
200 Posts
01/01/1900 as NULL
Nov 16, 2018 05:27 PM|sushsudh|LINK
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?
All-Star
43500 Points
15164 Posts
Re: 01/01/1900 as NULL
Nov 16, 2018 05:57 PM|PatriceSc|LINK
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 @TestUsually 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....
All-Star
38011 Points
15472 Posts
Re: 01/01/1900 as NULL
Nov 16, 2018 05:58 PM|mgebhard|LINK
Use COALESCE.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017