Incorrect Default Date

Question

Incorrect Default Date

Richard Proudfoot

SSC Enthusiast

Points: 112
More actions
January 6, 2009 at 6:22 pm

#96877

In a new installation of SQL Server 2000 the database is returning the date of the installation as the default date for empty date fields. In the previous installation the zero date (i.e. 1900) was returned.
What has caused this?
How do we revert to the correct database behaviour of returning the 1900 date?

Viewing 13 posts - 1 through 12 (of 12 total)

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

Richard Proudfoot SSC Enthusiast Points: 112 More actions · Answer 1

As I delve further into this I find that query results are varying between stored procedures - some giving the correct default value and others not.

I will investigate further. There is clearly something going on that we do not have control of.

Thanks for looking at this post. Unless you know of a specific fix, please don't spend time on this until I have looked into it a bit deeper.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 2

Why not just set a default on the column for the date that you want?

Shamless self promotion - read my blog http://sirsql.net

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Richard Proudfoot (1/6/2009)
In a new installation of SQL Server 2000 the database is returning the date of the installation as the default date for empty date fields. In the previous installation the zero date (i.e. 1900) was returned.
What has caused this?
How do we revert to the correct database behaviour of returning the 1900 date?

Exactly what do you mean by an 'empty date field', Richard? Surely this is NULL?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

Also could you post the queries that you expect are giving diff results.

With samples of how the results are different. 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Richard Proudfoot SSC Enthusiast Points: 112 More actions · Answer 5

Nicholas Cain (1/7/2009)
Why not just set a default on the column for the date that you want?

Nicholas, This is an option, but it doesn't resolve the underlying problem. We need to understand what is causing the problem.

Richard Proudfoot SSC Enthusiast Points: 112 More actions · Answer 6

Chris Morris (1/7/2009)
Exactly what do you mean by an 'empty date field', Richard? Surely this is NULL?

Chris, You are right, I probably should have said NULL. I was also trying to cover the instance where there is a '' (i.e. blank) value entered into a date field. I'm not sure how this is stored in the database - is it null, or is it stored as the default date 1900-01-01 00:00:00.000?

I'm sorry if I am a little imprecise, but I am not an expert in these matters!

Richard Proudfoot SSC Enthusiast Points: 112 More actions · Answer 7

Christopher Stobbs (1/7/2009)
Also could you post the queries that you expect are giving diff results.
With samples of how the results are different. 🙂

Christopher, I have constructed a query that demonstrates the problem

SELECT

j.Job_No as Job_no,

j.book_date as Job_Date,

j.Man_No as Manifest,

m.[datetime] as Manifest_Date

FROM

dbo.Job j

left outer join dbo.Job m on j.man_no = m.job_no

RESULTS

Job_no Job_Date Manifest Manifest_Date

50328 1899-12-30 00:00:00.000 50295 2003-07-07 09:54:00.000

127161 1899-12-30 00:00:00.000 2008-08-18 09:48:53.000

528713 1899-12-30 00:00:00.000 529131 2007-09-20 21:54:00.000

541025 2002-10-24 00:00:00.000 540538 2007-10-23 17:43:00.000

13 2002-11-04 00:00:00.000 2008-08-18 09:48:53.000

14 2002-11-04 00:00:00.000 2008-08-18 09:48:53.000

16 2002-11-04 00:00:00.000 2008-08-18 09:48:53.000

17 2002-11-04 00:00:00.000 2008-08-18 09:48:53.000

18 2002-11-04 00:00:00.000 2008-08-18 09:48:53.000

19 2002-11-04 00:00:00.000 2008-08-18 09:48:53.000

As you can see, where the job_date is null, the default date 1899-12-30 00:00:00.000 is returned. However, where the manifest_date is null, 2008-08-18 09:48:53.000 (the date this instance of SQL Server was installed) is returned. It looks like it is a problem with the join? But where is it getting 2008-08-18 09:48:53.000 from?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Richard, what are the results of the following two queries?

SELECT book_date as Job_Date, COUNT(*)

FROM dbo.Job

WHERE book_date IS NULL OR book_date = '1899-12-30 00:00:00.000'

GROUP BY book_date

SELECT [datetime] as Manifest_Date, COUNT(*)

FROM dbo.Job

WHERE [datetime] IS NULL OR [datetime] = '2008-08-18 09:48:53.000'

GROUP BY [datetime]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Richard Proudfoot SSC Enthusiast Points: 112 More actions · Answer 9

Chris,

Here are the results:

Job_Date Count

1899-12-30 00:00:00.0004

Manifest_Date Count

2008-08-18 09:48:53.0001

I have also run these queries:

SELECT Job_No,book_date,[datetime] as Job_Date

FROM dbo.Job

WHERE book_date IS NULL OR book_date = '1899-12-30 00:00:00.000'

Job_No book_date Job_Date

50328 1899-12-30 00:00:00.0002003-07-04 00:00:00.000

127161 1899-12-30 00:00:00.0002004-03-31 00:00:00.000

528713 1899-12-30 00:00:00.0002007-09-20 10:03:00.000

1899-12-30 00:00:00.000 2008-08-18 09:48:53.000

SELECT Job_No,book_date,[datetime] as Job_Date

FROM dbo.Job

WHERE [datetime] IS NULL OR [datetime] = '2008-08-18 09:48:53.000'

Job_No book_date Job_Date

1899-12-30 00:00:00.0002008-08-18 09:48:53.000

Incidentally, there are 685,000 records in the job table.

Thanks for your persistence and help on this.

Regards, Richard

Richard Proudfoot SSC Enthusiast Points: 112 More actions · Answer 10

Chris,

I suddenly see the answer from the result of the second query. The job number is always unique except if there is a null job number field (which there should not be). A blank job number was entered accidentally during testing on the new installation on the same day as the new installation (must change it to NOT NULL). The join is giving me the 2008-08-18 09:48:53.000 date for every job that does not have a manifest.

Problem solved. Many thanks for pointing me in the right direction.

Regards,

Richard

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Well done Richard, top work.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Richard Proudfoot SSC Enthusiast Points: 112 More actions · Answer 12

Chris,

Problem solved.

Your queries gave me the answer. The problem is the blank job number that was inserted on the day of the new server installation (the field should be NOT NULL) during testing.

The join was giving the date (2008-08-18 09:48:53.000) of this blank job number to every job that was not manifested.

Many thanks for pointing me in the right direction.

Regards, Richard