Setting a DateTime Field to NULL

Question

Setting a DateTime Field to NULL

bwillyerd

Right there with Babe

Points: 730
More actions
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.

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

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

guarddata SSCertifiable Points: 6583 More actions · Answer 1

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-

SQLBill SSC Guru Points: 51440 More actions · Answer 2

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

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

SQLBill SSC Guru Points: 51440 More actions · Answer 4

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

-SQLBill

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

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

Russel Loski SSCertifiable Points: 7059 More actions · Answer 6

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 7

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

bwillyerd Right there with Babe Points: 730 More actions · Answer 8

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

Bill