1

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.

5

Besides Gary's answer above, 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 '16 at 14:21
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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