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

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.
About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Your Comments

*

Protected with IP Blacklist CloudIP Blacklist Cloud

Follow

Get every new post delivered to your Inbox

Join other followers: