I ran into a problem where Excel and SQL Server have different "zero day" date conventions, so that all the dates currently in one of my tables are two days ahead of where they should be. Is there an easy way to update all the dates?

I know I can do something like

SELECT DatabaseID, P60_Date, DATEADD(day, -2, P60_Date) AS NEW_P60_Date FROM US_P60

But I don't know how to UPDATE all the values in P60_Date with the new dates.


It should be this simple:

SET u.P60_Date = DATEADD(DAY, -2, u.P60_Date)
FROM dbo.US_P60 AS u

If you want to be sure, you can do:

INTO dbo.US_P60_bk
FROM dbo.US_P60

Then validate the update is what you want. If it is, drop the _bk table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.