Date Time Default Value


We have a table with five columns and one of them is a DateTime column. While inserting rows through a trigger, the value for the datetime column is inserted as '1900-01-01 00:00:000' when the value is NULL.

Kindly clarify & suggest fix.

Rgds Mohan Kumar VS

more ▼

asked Jun 27, 2011 at 04:19 AM in Default

Mohan Kumar gravatar image

Mohan Kumar
204 18 20 21

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

For a datetime to have that value, then it will have been set to 0. If the field is nullable and you have set it to null, then it will return null.

Check if the table has default constraint.
more ▼

answered Jun 27, 2011 at 04:23 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

The datetime column is designed as AllowNull. There is no default constraint.
Jun 27, 2011 at 04:30 AM Mohan Kumar
Maybe something in the trigger code is replacing null with 0 then? Can you post the code?
Jun 27, 2011 at 04:34 AM Kev Riley ♦♦
Thanks... The problem was within the trigger as you mentioned. Thanks again.
Jun 27, 2011 at 05:36 AM Mohan Kumar
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 27, 2011 at 04:19 AM

Seen: 567 times

Last Updated: Jun 27, 2011 at 04:19 AM