T-SQL Trigger, replace 1/1/1900

[Bulldog13]

Good afternoon all,

I am running into a problem with my program where when I do an insert into TABLE_A with fields ID (PK), NAME (Varchar), SPECIAL_DATE (datetime).

When I insert a null or empty into SPECIAL_DATE SQL Server 2005 gives it a value of 1/1/1900. I know why it does this...because a null or 0 datetime value translates to 1/1/1900.

I need a trigger that can detect the 1/1/1900 0 value and replace it with a SqlDateTime.null .

I know I can detect a null value and insert a SqlDateTime variable with a value of SqlDateTime.null instead which will fix the issue, but the code is too large for that at this point.

How do I do it both for Insert and After Update ?

 

[WannaFly]

Are you sure the SPECIAL_DATE column is nullable? datetime should accept null.

 

[clamum]

That seems weird to me that 01/01/1900 gets put in that column when you insert a null. I am working on an application that uses SQL Server 2005 as data storage and in the data access layer I do a check on any date fields of my objects. If the date equals DateTime.MinValue, I insert DBNull.Value and if not then the date simply gets inserted. None of the tables have any 01/01/1900 in them. This is using ASP.NET by the way. What language are you working with?

EDIT: Yeah, WannaFly's suggestion is definitely the first thing to check.

 

[torpid]

Isn't datetime.Minvalue 1/1/0001?

 

[clamum]

Originally posted by: torpid
Isn't datetime.Minvalue 1/1/0001?

Yeah, it is. Brain fart... good thing tomorrow's Friday.

 

[Bulldog13]

Originally posted by: clamum
That seems weird to me that 01/01/1900 gets put in that column when you insert a null. I am working on an application that uses SQL Server 2005 as data storage and in the data access layer I do a check on any date fields of my objects. If the date equals DateTime.MinValue, I insert DBNull.Value and if not then the date simply gets inserted. None of the tables have any 01/01/1900 in them. This is using ASP.NET by the way. What language are you working with?

EDIT: Yeah, WannaFly's suggestion is definitely the first thing to check.

Yes the field allows nulls.

I am working with ASP.NET.

"DateTime values are stored as numbers. A SQL Server DateTime value can
represent a date between January 1, 1900, through June 6, 2079. Whe you put
a DateTime into a column, you put it in as a string. Putting a blank string
in apparently is interpreted as 0, which would represent January 1, 1900. A
blank string is not a null value. Put NULL into the field instead, and make
sure that the column accepts NULL values." - some webpage.

Issue is the code is too large at this point to progmaticaly make the adjustments from empty strings to nulls, hence the need for a trigger...or maybe even a constraint ?

 

[JACKDRUID]

create trigger trg_insert
on yourtable
for insert
as
...


create trigger trg_afterupdate
on yourtable
for update
as
...

 

[Kntx]

As an aside, the minimum date in SQL Server is 1/1/1753.