In this post, we’ll go briefly the process of how you would update all rows in a SQL Server 2008 table such that a particular date column will be moved back 1 hour in time. This is actually pretty simple, but being that I typically do my work in the ORM layer (that is LINQ2SQL or Entity Framework), I just don’t do much of this. The process I’m going to follow is first to use Microsoft SQL Server Management Studio to make sure I know what I’m doing with the Sql Server 2008 functions, then add it to an UPDATE statement.
So, first thing I do is fire up Microsoft SQL Server Management Studio, create a new query (it does not matter what database because I’m going to simply execute some TSQL.
Basically, I’m going to write some simple TSQL to create a variable with today's date (and time) in it, then use the function DATEADD and create a new date with 1 hour less. Here is the code:
DECLARE @MyDate datetime
DECLARE @NewDate datetime
SET @MyDate = GetDate();
SET @NewDate = DATEADD(HOUR,-1,@MyDate);
PRINT @MyDate
PRINT @NewDate
And, after pasting it in Microsoft SQL Server Management Studio, you can see the output when pressing execute.
Now, we’ve proven to ourselves we no how to subtract an hour, let’s apply it to a simple SQL Statement and presto, problem solved. Here is the final simple SQL that does the job.
UPDATE Load
SET ProcessedDate=DATEADD(HOUR,-1,ProcessedDate)
WHERE Id=954249