Comments section for the select and select into commands include this information:
“Because functions allow null values, any column in the select list that contains a function other than convert or isnull allows null values.”
Datatypes and nullability are implicitly assigned to literal values when the select into is used, such as:
select x = getdate() into mytable
This results in a non-nullable column, regardless of whether allow nulls by default is on or not. It depends upon how the select commands are used and with what other commands within the syntax.
The convert() syntax allows you to explicitly specify the datatype and nullability of the resulting column, not the default.
Workaround: Wrap getdate() with a function that does result in a null, such as:
select x = nullif(getdate(), "1/1/1900") into mytable
Or, use the new convert() syntax:
select x = convert(datetime null, getdate()) into mytable
