November 26, 2002 at 6:12 pm
Is there a way to store NULL in a datetime column? I've tried but It defaults to Jan 01, 1900.
Thanks in Advance?
Greg
November 26, 2002 at 6:24 pm
Yes,
make sure it can store nulls.
but try below,it puts a null
into a datetime field.
create table ##test
(myddd datetime null, myaaa varchar(10) )
insert into ##test values ( NULL, '111')
select * from ##test
drop table ##test
What's the business problem you're trying to solve?
November 26, 2002 at 9:50 pm
It works if you specify NULL, If you specify nothing( Single Quotes with a space) it enters the default Jan 01 1900.
Run the script like this
create table ##test
(myddd datetime null, myaaa varchar(10) )
insert into ##test values (' ', '111')
select * from ##test
drop table ##test
and the results will be...
(1 row(s) affected)
myddd myaaa
------------------------------------------------------ ----------
1900-01-01 00:00:00.000 111
(1 row(s) affected)
How do I get around this?
November 27, 2002 at 3:00 am
I'm a bit confused, are you saying that when you store your records you do not know if the date is blank or contains a date.
If this is the case then try
declare @testdate varchar(10)
set @testdate = ' '
create table ##test
(myddd datetime null, myaaa varchar(10) )
insert into ##test values ((CASE WHEN RTRIM(@testdate)='' THEN NULL ELSE @testdate END), '111')
select * from ##test
drop table ##test
Far away is close at hand in the images of elsewhere.
Anon.
November 27, 2002 at 7:57 am
Yes using '' on a column that is datetime defaults to the lowest value which is 1/1/1900. You need to insert with the keyword NULL in the position instead.
You cannot stop it and test for the date will return a valid date.
How are you building your insert so that it would use '' instead of NULL?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply