SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t (id int)
insert into t (id) values (1), (2)
2 rows affected

select *
from t

id

1

2

alter table t add datem date default getdate()
select * 
from t

id
datem

1
null

2
null

insert into t (id) values (3) 
1 rows affected

select * from t

id
datem

1
null

2
null

3
2024-05-20

alter table t add datem2 date not null default getdate()
select *
from t

id
datem
datem2

1
null
2024-05-20

2
null
2024-05-20

3
2024-05-20
2024-05-20

id	datem
1	null
2	null

id	datem
1	null
2	null
3	2024-05-20