locked
01/01/1900 as NULL RRS feed

  • 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?

    Friday, November 16, 2018 5:27 PM

Answers

  • 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....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 16, 2018 5:57 PM

All replies

  • 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....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 16, 2018 5:57 PM
  • 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

    Friday, November 16, 2018 5:58 PM