avoiding 1/1/1900 in SQL SERVER?
-
while updating or inserting a record in SQL SERVER, by default the DATETIME variable getting 1/1/1900. i need a trigger, so that whenever UPDATE or INSERT is made in a particular table, and if datetime consist of 1/1/1900, those values should replace as NULL.... can anyone provide the trigger for this? or is there anyother solution? Currently i m using ASP.NET with C# (.net 2003, 1.1 framework) - KARAN
-
while updating or inserting a record in SQL SERVER, by default the DATETIME variable getting 1/1/1900. i need a trigger, so that whenever UPDATE or INSERT is made in a particular table, and if datetime consist of 1/1/1900, those values should replace as NULL.... can anyone provide the trigger for this? or is there anyother solution? Currently i m using ASP.NET with C# (.net 2003, 1.1 framework) - KARAN
I'm sorry I can write the whole Trigger for u but I'm gonna give u a few clues to start with . First you need to make an INSTEAD OF trigger for insert and updates in that you will use the updated and inserted values. if the date in the inserted table matches 1/1/1900 dont issue an insert/update statement. Otherwise execute the insert statement. I hope this will help you
Rocky Success is a ladder which you can't climb with your hands in your pockets.
-
while updating or inserting a record in SQL SERVER, by default the DATETIME variable getting 1/1/1900. i need a trigger, so that whenever UPDATE or INSERT is made in a particular table, and if datetime consist of 1/1/1900, those values should replace as NULL.... can anyone provide the trigger for this? or is there anyother solution? Currently i m using ASP.NET with C# (.net 2003, 1.1 framework) - KARAN
If you have full control of the application's source code, a better solution than a Trigger would be to check the value in the application code for 1/1/1900. If it matches 1/1/1900, then set the parameter value for your SqlParameter object to DbNull.Value instead of the actual datatime value. If you aren't using SqlParameter objects (which you should be) then set the value of the string to "NULL". Example 1:
using(SqlCommand cmd = new SqlCommand(.. set ctor arguments ..)){ // setup cmd object cmd.Properties.Add("@date", SqlDbType.DateTime); if(myDateVariable.ToShortDateString() == "1/1/1900") cmd.Properties["@date"] = DbNull.Value; else cmd.Properties["@date"] = myDateVariable; }Example 2:StringBuilder sql = new StringBuilder("INSERT INTO myTable(dateColumn) VALUES("); if(myDateVariable.ToShortDateString() == "1/1/1900") sql.Append("NULL"); else sql.Append(String.Format("'{0}'", myDateVariable));NOTE: The second example will expose you to Sql Injection attacks if the value you are concatenating to your SQL statement is a String variable, so I recommend the first method for all SQL statements.Mark's blog: developMENTALmadness.blogspot.com