Post reply

Inserting NULL into datetime Field

  • 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.

  • February 2, 2004 at 1:41 pm

    #492542

    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  

  • February 2, 2004 at 1:46 pm

    #492545

    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]

  • February 2, 2004 at 1:49 pm

    #492548 

    Is there a DEFAULT definition for the column?

  • February 2, 2004 at 1:59 pm

    #492551

    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.

  • February 2, 2004 at 4:20 pm

    #492564

    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

  • February 2, 2004 at 8:55 pm

    #492570

    >> 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? 

  • February 3, 2004 at 7:43 am

    #492615

    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

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

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