Skip to content

Using SqlServer 2008 and TSQL Subtract 1 Hour From All Values In a DateTime Column

Updated: at 02:12 PM

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.

image

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
 
That’s it! Hope this simple little post helps someone.
Check out the ORM (Object Relational Mapper) PRISMA. The database access method I use in all my projects