Odd date appearing

Question

Post reply

Odd date appearing

Keith Edwards

Default port

Points: 1446
More actions
May 6, 2004 at 10:20 am

#82756

Well I have run across an interesting problem with a date field that allows nulls. If an entry isn't put in the field when a record is added it automatically will put in a value of 1899-12-30 00:00:00.000 and occassionally a value of 1900-01-01 00:00:00.000 The application that is being used to enter the data references a stored procedure and it appears to be fine. Other date fields are in use and are fine. Has anyone seen this before or have any ideas of what I should check? thanks
Keith
BTW - I am running a SQL 2000 server.

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

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

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

Keith,

According to BOL from the 'Using Date and Time Functions' section:

'There are no separate time and date data types for storing only times or only dates. If only a time is specified when setting a datetime or smalldatetime value, the date defaults to January 1, 1900. If only a date is specified, the time defaults to 12:00 A.M. (Midnight).'

Maybe then the proc is being executed sometimes without a complete datetime entry which could explain this. The 1899 date could be explained by someone used a 'getdate() -1' or something similar. Something to look at anyway. Hope this helps.

My hovercraft is full of eels.

Glenda Baker SSC Enthusiast Points: 151 More actions · Answer 2

We had something similar happen. To handle this, we set the parameters in our in our stored procs and triggers to null if no values were passed to them. Now the field remains null if no value is passed in.

Example:

DECLARE @MyDate DateTime = null,

@MyInt Int = null,

@MyVarChar VarChar(50) = null

vadba SSChampion Points: 11133 More actions · Answer 3

The problem may be that the source dates your are inserting are not actually null, but blank strings. For example, this happens frequently when importing FoxPro or dBase data though DTS.

Try this:

drop table datetest

go

create table datetest (id int primary key, dt datetime null)

insert datetest (id, dt) values (1, getdate())

insert datetest (id, dt) values (2, '')

insert datetest (id, dt) values (3, getdate())

select * from datetest

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

You might want to check the source date ahead of time and change it to NULL:

declare @din varchar(20), @dout datetime

SET @din = ''

SET @dout = Convert(datetime, CASE @din WHEN '' THEN NULL ELSE @din END)

SELECT @dout

Mike

wodom Hall of Fame Points: 3813 More actions · Answer 4

We have seen the 1899 date often -- when transferring dates to or from MS Access. Access and SQL Server have different "base" or "zero" dates, and this is the result. I suspect many other applications, especially those that use the Jet database engine (things written in Visual Basic, etc.) will show the same phenomenon. Specifically (from the Access help text):

Dates and times are stored internally as different parts of a real number.

The value to the left of the decimal represents a date between December 30, 1899 and December 30, 9999, inclusive. Negative values represent dates prior to December 30, 1899.

The value to the right of the decimal represents a time between 0:00:00 and 23:59:59, inclusive. Midday is represented by .5.

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 5

The same behavior with date/time values and occurs in Delphi, too. If you try to read the value of a field/parameter using the AsDateTime property and the underlying value is NULL, VCL returns the "famous" 0 value (1899-12-30 00:00:00.000).

Regards,

Goce Smilevski.

Keith Edwards Default port Points: 1446 More actions · Answer 6

Thanks Goce. I found out the aplication that is causing the oddity is programmed in Delphi. I was just curious how did you end up working around the issue? thanks

Keith

wodom Hall of Fame Points: 3813 More actions · Answer 7

It may be that when Delphi was created, they intentionally chose the same "base" date used by Access.

As for dealing with the problem, when we are using the native table display features of Access (or whatever), it's a user education issue so they'll know it means "no date given." In program code, when checking for "no date," sometimes we have to include a check for the 12/30/1899 value also, and consider it the same as null or zero.

It really IS zero internally in Access (and presumably Delphi too); only the act of using the value in a date context makes it show up as 1899. ... Which implies that perhaps the equivalent of a CAST would help, depending on the details of the code.