Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
When I perform an update on certain records, i.e.:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
I am finding that *some* user id's result in the follwing error:
"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."
mytable view has a number of data fileds. All the data fields are of type
smalldatatime.
When i compare the user record of a userid that causes an error to one that
doesnt cause an error, the dates do vary, where some date fields have NULL's
or correctly formated smalldatatime values (yes I know about the restriction
of smalldatetime to range between 1900 and 2079).
The odd thing is that even if i am updating a non-date field within the
view, the above arithmetic error still occurs.
My trigger looks like the following:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
DECLARE @.mydate datetime
SELECT @.mydate = GETDATE()
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0),
date1 = inserted.date1,
date2 = @.date+30
FROM inserted
WHERE mytable.userid = inserted.userid
Am i getting this error because i am mixing a date2 fiels (which is of type
smalldatetime) with @.date (which is of type datetime) ?
Any help most appreciated.Do you need the extra ms or time range? If not try:
DECLARE @.mydate smalldatetime
SELECT @.mydate = GETDATE()
SELECT @.mydate
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||Also, drop the SELECT @.mydate -- was just for testing. Basically using the
SMALLDATETIME data type instead of DATETIME.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
> Do you need the extra ms or time range? If not try:
> DECLARE @.mydate smalldatetime
> SELECT @.mydate = GETDATE()
> SELECT @.mydate
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||Sorry, I meant to say:
mytable view has a number of *date* fields. All the *date* fields are of
type
smalldatatime.
.. late night
cheers, peter
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||thanks ill try that when i get to work
I hope its as obvious as changing smalldatetime to datetime!
cheers, peter
> Also, drop the SELECT @.mydate -- was just for testing. Basically using
> the SMALLDATETIME data type instead of DATETIME.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
>
