Insert NULL or ' ' in SmallDateTimefield

Question

Insert NULL or ' ' in SmallDateTimefield

Hursh 191

When I insert NULL or empty string in a nullable SmallDateTime field; it inserts 1900-01-01 00:00:00
How do I set it to either empty or null?

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Naomi Nosonovsky 8,881

How exactly you're inserting null? NULL should work fine, e.g. insert into myTable (MyDateTimeColumn) values (NULL)

Empty '' string converts to '1900-01-01' by default.

Hursh 191 Reputation points

2022-07-21T12:38:48.52+00:00

My bad...
I needed to insert blank in SmallDateTime; not the NULL.
Naomi Nosonovsky 8,881 Reputation points

2022-07-21T12:42:26.533+00:00

Smalldatetime doesn't have blank value. It's either date value or NULL. If you want to display NULL as empty string, it's the presentation layer and you can format result back accordingly.
Olaf Helper 47,581 Reputation points

2022-07-21T12:44:46.677+00:00

blank in SmallDateTime;

A date value can't be "blank" (only strings can), it has a valid date as value or NULL.

Answer 2

Olaf Helper 47,581

Insert NULL works as expected and an empty string gets contered to 1900-01-01, it's the smallest possible date value for smalldatetime

declare @test table(myDate smalldatetime NULL);  
  
insert into @test (myDate) values (NULL);  
insert into @test (myDate) values ('');  
  
select *  
from @test

Share via

Insert NULL or ' ' in SmallDateTimefield

1 additional answer

Your answer