Solved

SQL modify date by two days using update query

Posted on 2008-09-30
5
640 Views
Last Modified: 2012-05-05
Is it possible to use an update query to change a time value on every row by 2 days, say using the dateadd function?
0
Comment
Question by:pansophy
  • 2
  • 2
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22608192
update t
set datefield = dateadd(d, 2, datefield)
from tablename t
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22608438
another easy ways is:

update table_name
set column_name = column_name - 2 /* or + 2 */

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22608455
assuming that your system is setup to subtract days rather than hours or months, etc.
0
 

Author Closing Comment

by:pansophy
ID: 31501667
thanks!
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22608595
http://msdn.microsoft.com/en-us/library/aa276859(SQL.80).aspx



B. Use the addition operator to add days to date and time values
 

This example adds a number of days to a datetime date.
 

USE master

GO

SET NOCOUNT ON

DECLARE @startdate datetime, @adddays int

SET @startdate = '1/10/1900 12:00 AM'

SET @adddays = 5

SET NOCOUNT OFF

SELECT @startdate + 1.25 AS 'Start Date', 

   @startdate + @adddays AS 'Add Date'
 

Here is the result set:
 

Start Date                  Add Date                    

--------------------------- --------------------------- 

Jan 11 1900  6:00AM         Jan 15 1900 12:00AM         
 

(1 row(s) affected)

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL previous 5 24
TSQL - IF ELSE? 3 29
SQL Server memory sizing - reallocation 16 29
Update data using formula 22 21
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now