Inserting NULL into datetime Field

Question

Post reply

Inserting NULL into datetime Field

neddyflanders

SSC Eights!

Points: 972
More actions
February 2, 2004 at 12:39 pm

#107158

Hello, I am trying to insert a NULL value into a datetime field using an insert statement. The sample is
If (@letter = 'H')
       If not exists (Select * from Approval where Code_ID = @Code_Id)
    Begin
    insert into APPROVAL
    (CODE_ID,STATUS_DATE)
VALUES
    (@Code_Id,NULL)
END
I'm ending up with 1/1/1900 in the status_date field doing it this way. Thanks to anybody that can help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MikeMcDonald Say Hey Kid Points: 663 More actions · Answer 1

First reaction ... don't insert the null (it is superfluous) .. ie

insert into APPROVAL (CODE_ID) VALUES (@Code_Id)

Isn't 01 Jan 1900 the base time & date for smalldatetime?

However, I tested some code and it didn't reproduce your problem.... are you sure there's no default on the table?

declare @dt table (n int, dt1 datetime, dt2 smalldatetime)

insert into @dt (n, dt1, dt2) values (1, null, null)

insert into @dt (n) values (2)

select * from @dt

n dt1 dt2

1

2

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Yes, 01/01/1900 is SQL Server's reference date (day 0). All other dates are calculated as days before or after this date

I, too, cannot reproduce your case

if object_id('abc') is not null

drop table abc

go

create table abc(mydate datetime)

go

insert into abc(mydate) values(NULL)

insert into abc(mydate) values(0)

select * from abc

mydate

------------------------------------------------------

NULL

1900-01-01 00:00:00.000

(2 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Allen Cui-55137

SSC Guru

Points: 51650

February 2, 2004 at 1:49 pm

#492548

Is there a DEFAULT definition for the column?

neddyflanders SSC Eights! Points: 972 More actions · Answer 4

Nah, no default definition. Its datetime, 8, Allows NULLS. Another table is inserting into the table with Status_Date bya trigger. I tried putting NULL or ' '. NULL doesn't work and ' ' gives me 1/1/1900. I also need to put NULL, its in the specifications so thats not an option (Can't argue with specs.) Thanks for helping me out with this.

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 5

MikeMcDonald's point was that SQL Server automatically inserts NULL into any column not specified in the INSERT statement. The exceptions are if no columns are specified (in which case you need to populate all columns, except an identity column if you have one), or if there is a default value declared for the column as a part of the table definition.

Since you say you don't have a default value, you shouldn't need to specify the column at all; leave it off and see what happens.

R David Francis

Mike Good SSCertifiable Points: 7415 More actions · Answer 6

>> I'm ending up with 1/1/1900 in the status_date field doing it this way. <<

Neddyflanders, how are you getting this result? I believe all of the people who've replied are executing SELECT * FROM APPROVAL within Query Analyzer... is this what you're doing to see the 1/1/1900, or something else?

SQLBill SSC Guru Points: 51440 More actions · Answer 7

Neddy,

You state:

Another table is inserting into the table with Status_Date bya trigger

Check out the trigger, it might be the cause of your problem.

-SQLBill