add extra columns in a SELECT INTO statement

Question

I am doing a SELECT INTO statement to create a new table from some values in another table. I want to add two extra columns onto the newly created table (pol_eff_dt, pol_exp_dt) and make them all NULL initially (these columns also exist in the source table so they would otherwise pull that data). I also need to make sure they come over as DATETIME types. How can I cast them this way? I've tried a lot of things, but nothing will compile.

SELECT DISTINCT bnd_ser_nbr,
trans_nbr,
uws_product_cd,
bill_id,
NULL AS pol_eff_dt, 
NULL AS pol_exp_dt
INTO ER_RO_urs_prem_detail
FROM urs_prem_detail_interim

Answer 1

You can try casting the NULL explicitly as DATETIME, like so:

SELECT DISTINCT bnd_ser_nbr,
trans_nbr,
uws_product_cd,
bill_id,
CAST(NULL AS DATETIME) AS pol_eff_dt, 
CAST(NULL AS DATETIME) pol_exp_dt
INTO ER_RO_urs_prem_detail
FROM urs_prem_detail_interim

Demo here. In the demo, if you change the type that the 2 extra columns are cast to, and then try to assign a DATETIME value, you will get a conversion error.