Solved

SQL modify date by two days using update query

Posted on 2008-09-30
5
642 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

860 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