When does Date Field shows "1900-01-01 00:00:000"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

When does Date Field shows "1900-01-01 00:00:000"

Author

Topic

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-08 : 08:52:55

Hello Guys,
Would you be kind enough to let me know if a date field is left as '' will it show me "1900-01-01 00:00:000". What does this date refer to? Any help would be greatly appreciated. Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-08 : 09:01:21

Is this homework?


DECLARE @x int
SELECT @x = 0
SELECT CONVERT(datetime,@x)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-08 : 09:13:27

Hello,
No this is not my homework? My job is to test that what coding develper did so that i am getting this value in a date field? I will read the topic you mentioned.Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-08 : 09:48:24

OK, here's the "answer"

They did not want to make the datetime column Nullable, so the chose a "default" date that represent no date. Whis in SQL Server is 0, or the displayed above.

It seems to me that alot of people like to avoid nulls.

To me, it's just a bad design.

What the date column?

Date of Death?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-08 : 10:04:15

Thank You! What my senior developer told me that he has put '' in the date field that is why this value is populating.I need clarification from some other senior programmer that is why i had posted this question.I appreciate your help.Would you mind telling me when does 0 value populates and when does 1900-01-01 00:00:000 value populates.Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-08 : 10:17:29

An empty string ('') for a date would imply character data....which would be suspect in my book anyway. But


DECLARE @x char(1)
SELECT @x = ''
SELECT CONVERT(datetime,@x)

Does the same thing.

Can you give us the DDL of the tabel and a code snipet?

Read the link at the bottom of my sig to see how to communicate your probelm so we can talk on the same page.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-08 : 10:22:29

quote:
Originally posted by wshtrue

What my senior developer told me that he has put '' in the date field

"Senior Developer"...

What a scrub...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Crumpelfungus
Starting Member

5 Posts

Posted - 2005-06-08 : 10:33:50

Could also be a "default" value that the SENIOR DEVELOPER added to the table structure when the table was originally created.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-08 : 10:41:36

quote:
Originally posted by wshtrue

Thank You! What my senior developer told me that he has put '' in the date field

Just the facts maam

Know what...Until you see the code, and/or the DDL you have to take their word...which is one thing I never do when I trouble shoot

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-08 : 11:21:40

quote:
Originally posted by wshtrue

Thank You! What my senior developer told me that he has put '' in the date field that is why this value is populating.I need clarification from some other senior programmer that is why i had posted this question.I appreciate your help.Would you mind telling me when does 0 value populates and when does 1900-01-01 00:00:000 value populates.Thanks

Yikes ... hopefully your SENIOR DEVELOPER knows that he should not be storing empty strings in datetime columns and relying on implicit conversions ....

- Jeff

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-08 : 21:21:10

better to put NULL values...

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-08 : 22:59:07

Hi Guys,
Thank you so much for all your help. I just came back from the office and was reading your answers.I love your site.Actually there were three values he was putting one as NULL other as '' and third as valid date.'' was displaying 1900:01:01 00:00:000
Thanks once again

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-09 : 09:01:55

I hate to harp on this, but a SENIOR DEVELOPER ? Are you sure? MIght be time to look at promoting one of your junior developers.

One thing you might want to do as a DBA to protect yourself from the .. uh ... "expertise" ...of this SENRIOR DEVELOPER is to add a CHECK constraint to that column so that "not applicable" values are either Null or '' (depending on which you and the developer agree upon), but not a mixture of both.

- Jeff

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-09 : 09:10:46

Hi Jeff,
You think that this is the time to promote a junior developer so that means my time has come.LOL! He is a Senior DBA.
That day is also not far since i have beome a member of your site so i am learning a lot.Thanks! I appreciate it.

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-09 : 09:22:01

Hi Jeff,
One more question please you said "CHECK constraint to that column" Can you please give me one example may be today i can suggest your idea but i should be crystal clear what am i talking about.I would really appreciate your help.Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-09 : 09:28:26

Do you know what books online is?

look up create table...it's all in there...

It prevents what data can go into a particular column.

That said, I would imagine if you enforce that constraint fo this date column, you'll have code blowing up all over the place.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-09 : 09:42:12

Hello Brett,
Yes! I know what book online is and i will check it today. Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-09 : 10:24:49

>>That said, I would imagine if you enforce that constraint fo this date column, you'll have code blowing up all over the place

Exactly! That's what you want. Then you are sure the applications are not putting bad data in your table, and it will be clear where to change code to fix this behavior when it occurs.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-09 : 10:29:46

quote:
Originally posted by jsmith8858

>>That said, I would imagine if you enforce that constraint fo this date column, you'll have code blowing up all over the place

Exactly! That's what you want. Then you are sure the applications are not putting bad data in your table, and it will be clear where to change code to fix this behavior when it occurs.

- Jeff

OK, just to be clear.

You should NOT make this chnage unless your Executive chef...ummm, I mean your SENIOR DEVELOPER says it's OK.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2005-06-09 : 10:33:05

Got it Jefff and Brett! I will not change it without talking to my senior developer. Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-09 : 10:39:50

quote:
Originally posted by wshtrue

Got it Jefff and Brett! I will not change it without talking to my senior developer. Thanks

Jeff wants you to make the change.

Iw cause your inserts and updates to error out.

Do you use error handling in the code? If not you could get some unexpected results that could fubar your data.

Check doe that column name in your sprocs and see where these updates and inserts occur dor that column.

This will help you interogate your sprocs

http://weblogs.sqlteam.com/brettk/archive/2004/02/05/841.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources