Default date of 1/1/1900 - SQL Server Forums

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

SQL Server Development (2000)

Default date of 1/1/1900

Author

Topic

peterswan
Starting Member

8 Posts

Posted - 2002-05-08 : 09:45:01

Does anyone know how I can avoid SQL Server entering in a default date of 1/1/1900 instead of a null value? I'm trying to modify the data in a view using the DATEDIFF function and everything works fine until it encounters the 1/1/1900, at which point the values go sky high.

Please let me know, if possible, how to change this value to a null.

Any reply would be appreciated.

Thanks,

Peter Swanson

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-05-08 : 09:51:29

Peter,

I think you need to have a look at your front-end application. It seems to be inserting a 1 into your date column (SQL sees this as the date 1/1/1900).
To clean this out, you'll need to execute something like this:

UPDATE table
SET datefield = NULL
WHERE datefield = '1-Jan-1900'

Tim

peterswan
Starting Member

8 Posts

Posted - 2002-05-08 : 10:19:02

Hi Tim,

My front end is written in ColdFusion 5.0. Maybe it's because the values I'm entering in are empty strings, and it needs to convert to a date? Thanks much for the code you sent. Is that a stored procedure? Will that take care of the problem just once or every time a '1/1/1900' is encountered in the table? Can I execute it in SQL Server or should I put it in to my clent app, and run it every time an update is done?

Thanks for everything so far.

Peter Swanson

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-08 : 10:36:58

There should be a way in CF to pass a NULL instead of an empty string . . .the two have different meanings.

The other way to skin this cat is to define your view with a "where datecol <> '1/1/1900'" clause . . .

<O>

peterswan
Starting Member

8 Posts

Posted - 2002-05-08 : 10:45:43

Hi guys,

I appreciate all the help. I went with Tim's plan and it worked fine. I ran this query in my client app:

UPDATE serviceCall
SET servdate = NULL
WHERE servdate = '1-Jan-1900'

Now my question is whether for the other columns with the same problem, should I run separate queries or can I fit them all into one?

I'll try the other suggestion as well, and modify my view with the "where datecol <> '1/1/1900'"

Thanks for everyone's help,

Peter Swanson

peterswan
Starting Member

8 Posts

Posted - 2002-05-08 : 11:01:55

Issue closed now. I added some queries to my client app to change the 1/1/1900 to a null.

thanks for everyone's help!

Peter Swanson

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-08 : 13:07:58

Just another option for you.. I had a simular problem in the past. A web page was constantly inserting strange values for dates and SQL server would interpret them as 1/1/1900
After running an update query simular what timmy suggested I then set a trigger on the table that looked something like

create trigger on table for insert
as
if (select datefield from inserted) = '1/1/1900' update table set datefield = null where idkey = (select idkey from inserted)

This little trigger formatted anything coming into the database as 1/1/1900 to null.

Just something else to consider

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-08 : 13:14:41

I'm tweaking M.E.'s trigger a little so that it will work with multi-row INSERTs too:

CREATE TRIGGER SomeTriggerName ON table FOR INSERT AS
IF EXISTS (select * FROM inserted WHERE datefield='1/1/1900')
BEGIN
UPDATE T SET datefield=Null
FROM table T INNER JOIN inserted I ON T.idkey=I.idkey
WHERE I.datefield='1/1/1900'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources