Consider the following code in Microsoft SQL Server 2012:

INSERT INTO [dbo].Production
SELECT [field1]
      ,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.

2 Answers 2


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

  • 1
    I would prefer your code because it uses only one Cast instead of two and is shorter. Anyway NULLIF is a shorthand for a CASE...
    – dnoeth
    Aug 3, 2016 at 14:21

You could use a case statement:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.