How do I subtract and update a date column in an update statement?

Question

How would I go about updating existing DateTime columns in an update statement? I'd like to subtract one day but I get an error message saying that I cannot use DateAdd in an update (something about an overflow).

I came up with this query but of course it will not execute.

begin tran upd

  update answer 
  set SentForApprovalAt = DateAdd(day, -1, approvedAt)
  where 
   approvedAt > '1753-01-01'
   and approvedAt < modifiedAt

 commit tran upd

What do I do?

Answer 1

The reason you're receiving this error is likely because the date you're attempting to subtract 1 from is causing the resultant value to be less than the minimum value for a TSQL datetime.

Using the following testcase:

CREATE TABLE answer
(
 SentForApprovalAt DATETIME NULL,
 ApprovedAT DATETIME,
 ModifiedAT DATETIME
)

/* The query will work for this record */
INSERT
INTO   Answer 
       (sentforapprovalat, approvedat, modifiedat)
VALUES (null, '1800-01-01 00:00:00.000', GETDATE())

/* The query will error with 'Adding a value to a 'datetime' column 
   caused an overflow.' for this record */
INSERT 
INTO   Answer
       (sentforapprovalat, approvedat, modifiedat)
VALUES (null, '1753-01-01 01:00:00.000', GETDATE())

Answer 2

Why not approvedAt > '17530102' so you don't go before the datetime boundary

And don't use '1753-01-02' because it can be interpreted as 2nd Feb 1753 for UK english and other settings