Post reply

Setting a DateTime Field to NULL

  • October 27, 2003 at 8:01 am

    #83389

    I have this proc:

    CREATE PROCEDURE [spUpdAssetTable]

    (@AssetID [int],

    @ReleaseDate [datetime] = NULL,

    @LienDate [datetime] = NULL,

    @oc [varchar](255),

    @Plate [varchar](255),

    @YR [int],

    @Make_Model [varchar](255),

    @First_Legal [varchar](255),

    @Comments [text],

    @vin [varchar](255),

    @Proceeds [float],

    @Code [int])

    AS UPDATE [NWEP].[dbo].[AssetTable]

    SET [ReleaseDate] = @ReleaseDate,

    [LienDate] = @LienDate,

    [OC] = @oc,

    [Plate] = @Plate,

    [YR] = @YR,

    [Make_Model] = @Make_Model,

    [First_Legal] = @First_Legal,

    [Comments] = @Comments,

    [VIN] = @vin,

    [Proceeds] = @Proceeds,

    [Code] = @Code

    WHERE

    ( [AssetID] = @AssetID)

    GO

    If the value sent to either of the DateTime fields comes in as '' then 1/1/1900 is displayed in the record. I would really like NULL to be the value. Some instances may require the date to be deleted from an existing record, so the SQL string has the '' place holder in it. I believe it is the place holder that is forcing the creation of the 1/1/1900 in the field.

    Am I wrong? How do I set the field to NULL? OR do I have the code on the displaying ASP page show blank if the record actually holds 1/1/1900?

    TIA

    Bill.

  • October 27, 2003 at 8:26 am

    #479679

    My guess is that the database properly holds the NULL value. The last time I saw this problem, I had to play with the settings in the ADO recordset object that would convert the DateTime to zero (1/1/1900) I think this is an ASP issue...check through QA to be sure.

    Guarddata-

  • October 27, 2003 at 10:14 am

    #479717

    I don't believe you can use NULL with DATETIME or SMALLDATETIME datatypes. DATETIME and SMALLDATETIME have defaults so that means NULL isn't accepted/allowed (why have a default if you allow NULLs).

    -SQLBill

  • October 27, 2003 at 10:31 am

    #479724

    quote:

    I don't believe you can use NULL with DATETIME or SMALLDATETIME datatypes. DATETIME and SMALLDATETIME have defaults so that means NULL isn't accepted/allowed (why have a default if you allow NULLs).

    -SQLBill

    Any data type can be nullable. Any data type other than timestamp (aka rowidentifier) or one assigned the identity property can optionally have a default constraint. The temporal data types do not have default constraints until you create them, other than a default of NULL if the column is nullable.

    The issue here is that an empty string is not the same as NULL. The implicit conversion of an empty string to a temporal data type is the zero date, i.e. 1900-01-01. This doesn't differ from other such conversions, e.g.:

    

    CAST('' AS int)

    --Jonathan



    --Jonathan

  • October 27, 2003 at 11:44 am

    #479733

    Thank you Jonathan. My understanding was that a column wouldn't accept a NULL if a default was set.

    -SQLBill

  • October 27, 2003 at 12:55 pm

    #479754

    quote:

    Thank you Jonathan. My understanding was that a column wouldn't accept a NULL if a default was set.

    -SQLBill

    ...and it can, of course.

    I wasn't as precise as I should have been, given the issue at hand. In sending parameter values to a SP, any default in the parameter list is used only when that parameter value is not instantiated by the calling program. In other words, the front-end would need to set an enumerated parameter of the command object to empty in order for the default in the SP to be used. This is very different from instantiating the parameter to '' or Null. When working with nullable columns, this can be confusing...

    --Jonathan



    --Jonathan

  • October 28, 2003 at 3:06 am

    #479826

    I did a simple test: run query

    select convert (datetime, '')

    The results were 1900-01-01 00:00:00.000

    I think that it is casting '' to datetime and converting it to 1900-01-01.

    The easiest fix is:

    Set [ReleaseDate] = nullif(@ReleaseDate, ''),

    [LienDate] = nullif(@LienDate , ''),

    ...

    Russel Loski, MCSD

    Russel Loski

    Russel Loski, MCSE Business Intelligence, Data Platform

  • October 28, 2003 at 6:41 am

    #479858

    quote:

    I did a simple test: run query

    select convert (datetime, '')

    The results were 1900-01-01 00:00:00.000

    I think that it is casting '' to datetime and converting it to 1900-01-01.

    As I wrote...

    quote:

    The easiest fix is:

    Set [ReleaseDate] = nullif(@ReleaseDate, ''),

    [LienDate] = nullif(@LienDate , ''),

    ...

    That presumes that 19000101 will never be a meaningful date. I try and avoid "flag" values like this, particularly when, as here, their use is not obvious or advertant. The better fix is to have the front-end properly set the parameter's value to NULL rather than '' and remove the defaults from the SP as they're not being used in any case.

    But with your method, why force the implicit conversion from char? Just use:

    

    SET ReleaseDate = NULLIF(@ReleaseDate, 0),

    LienDate = NULLIF(@LienDate , 0),...

    --Jonathan



    --Jonathan

  • October 28, 2003 at 8:20 am

    #479893

    Thank you all for your input. The NULLIF function works wonderfully.

    Bill

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

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