Getting rid of 1900-01-01 00:00:00.000

_{Wednesday, May 24, 2017 11:20 AM}

Hello-

I’m having a weird issue with getting rid of NULL values and making them blank, instead it’s setting my field to 1900-01-01 00:00:00.000. I know why this is happening but I don’t know how to get around it. My code works fine by running it as a select with just a simple case statement but my requirements are forcing me to run this as an update.

Column type is datetime

UPDATE [Atemp].[dbo].[CUST] SET AccountCancellationDate=
CASE WHEN p.Status='C' AND ABC.PRODUCT__C IN ('CCC','XXX') THEN ISNULL(CONVERT(VARCHAR(23),ABC.Product_Cancellation_Date__c,121),'')
     ELSE ISNULL(CONVERT(VARCHAR(23),ABC.Product_Cancellation_Date__c,121),'') END AS AccountCancellationDate, 
     FROM [Atemp].dbo.tblCust p
INNER JOIN [Atemp.[dbo].[P1ASellers] P1 
ON P1.CustID=p.CustID
INNER JOIN [DBSERVER].ABC.SF.tblProduct__c CRS
   ON ABC.Account_Number__c=CAST(P.CustID AS VARCHAR)

I tried using running the following after the above update: UPDATE [Atemp].[dbo].[CUST] SET AccountCancellationDate=''

 Where AccountCancellationDate=1900-01-01 00:00:00.000 but that field doesn’t change

What in the world am I doing wrong, I’ve searched high and low for a solution and there are tons of others having this same issue but none of the posted solutions have worked for me so I’m posting my code in hopes someone can point out why I’m doing wrong. I’m about to pull out my hair!! J  

_{Wednesday, May 24, 2017 11:26 AM}

In the SQL Server world, an empty string for a datetime literal is the default datetime value ('1900-01-01 00:00:00.000'). Specify the keyword NULL to set the value to NULL:

UPDATE [Atemp].[dbo].[CUST] 
SET AccountCancellationDate = NULL
WHERE AccountCancellationDate = '1900-01-01 00:00:00.000';

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

_{Wednesday, May 24, 2017 11:27 AM}

You cannot insert blank ('') into date time type column. Try this and it will clarify what you are doing...

create table dbo.TestDate
(
    Datum datetime null
);


insert into dbo.TestDate (Datum)
values ('');

insert into dbo.TestDate (Datum)
values (null);

insert into dbo.TestDate (Datum)
values ('20170525');

select
*
from dbo.TestDate;

Insert null into date time column instead of blank ('').

HTH, Regards, Dean Savović, www.comminus.hr, www.savovic.com.hr

_{Friday, May 26, 2017 2:49 PM}

Hi There,

Check if 'AccountCancellationDate' allows null, before you run the update statement. 

UPDATE [Atemp].[dbo].[CUST] SET AccountCancellationDate=
CASE WHEN p.Status='C' AND ABC.PRODUCT__C IN ('CCC','XXX') THEN ISNULL(CONVERT(VARCHAR(23),ABC.Product_Cancellation_Date__c,121),NULL)
     ELSE ISNULL(CONVERT(VARCHAR(23),ABC.Product_Cancellation_Date__c,121),NULL) END AS AccountCancellationDate, 
     FROM [Atemp].dbo.tblCust p
INNER JOIN [Atemp.[dbo].[P1ASellers] P1 
ON P1.CustID=p.CustID
INNER JOIN [DBSERVER].ABC.SF.tblProduct__c CRS
   ON ABC.Account_Number__c=CAST(P.CustID AS VARCHAR)