casting empty string as null datetime 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.
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)?