casting empty string as null datetime

Question

Consider the following code in Microsoft SQL Server 2012:

INSERT INTO [dbo].Production
SELECT [field1]
      ,[field2]
      ,cast([datefield] as datetime)
FROM [RAW].Staging

The staging table is loaded with data from a CSV file. So in some cases, rather than having NULL fields we end-up with empty fields. As a result the datefield gets converted to a 1900-01-01 date when being inserted over the Production table.

I was wondering if there is something that I could do during the insert code above to ensure that datefield is set to NULL if the field is NULL or empty when coming from the Staging table.

Answer 1

Besides Gary's answer, I came up with the following:

cast(NULLIF([expected_discharge_dttm],'') as datetime)

Can anyone foresee an issue with this code (or Gary's use of CASE)?

Answer 2

You could use a case statement:

INSERT INTO [dbo].Production
SELECT [field1]
  ,[field2]
  ,CASE WHEN cast([datefield] as datetime) = '1900-01-01' THEN NULL ELSE cast([datefield] as datetime) END
FROM [RAW].Staging