How to Use a SQL Update Statement to Add 1 Year to a Datetime Column

How can I use a SQL UPDATE statement to add 1 year to a DATETIME column?

There is in fact a DATEADD statement in T-SQL, you can find it here

UPDATE Procrastination SET DropDeadDueDate = DATEADD(yyyy,1,DropDeadDueDate)

EDIT: You could use year, yy, or yyyy for the first argument of DATEADD.

SQL Server Update statement to change year and month of a datetime column

Try this, just replace your_table_name with each table you want to apply this to.

UPDATE your_table_name
SET TimeOccurred = 
    CAST( CAST( DATEPART( YEAR, GETDATE() ) AS VARCHAR( 4 ) ) + '-' +
         CAST( DATEPART( MONTH, DATEADD( MONTH, -1, GETDATE() ) ) AS VARCHAR( 2 ) ) + '-' + 
         (CASE 
            WHEN DATEPART( DAY, TimeOccurred ) IN ( 29, 30, 31 ) 
            THEN '25'
            ELSE CAST( DATEPART( DAY, TimeOccurred ) AS VARCHAR( 2 ) )
         END) +
         RIGHT( TimeOccurred, 8 )
     AS SMALLDATETIME )

Output:

+---------------------+
|    TimeOccurred     |
+---------------------+
| 2017-05-28 13:03:00 |
| 2017-05-28 13:03:00 |
| 2017-05-28 13:03:00 |
| 2017-05-28 13:03:00 |
| 2017-05-28 13:03:00 |
| 2017-05-25 11:02:00 |
| 2017-05-25 11:07:00 |
| 2017-05-25 11:12:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
| 2017-05-25 14:37:00 |
+---------------------+

Demo:
Rextester Demo

Update all date columns in SQL Server -1 day

The request below will update the rows you want by adding -1 days on each date:

UPDATE tbl_table 
SET    dates = Dateadd(day, -1, dates) 
WHERE  id IN ( 29695, 29700, 29701, 29702, 
               29703, 29704, 29705, 29706, 
               29707, 29708, 29709, 29710, 
               29711, 29712, 29713, 29714, 29715 )

DATEADD function takes 3 parameters:

1. the interval ( day, month, year ..)
2. the increment (the value to add or remove if negative)
3. an expression (wich is a datetime type)

See DATEADD documentation

Update date + one year in mysql

You could use DATE_ADD : (or ADDDATE with INTERVAL)

UPDATE table SET date = DATE_ADD(date, INTERVAL 1 YEAR) 

Add Years to existing datetime column to create a 2nd datetime column

would go yet with DATEADD

select StartTime
  , dateadd(year, 3, StartTime) as NewStartTime 
 from LNVTable;

SQL Fiddle

Update datetime values format

To update the existing values, you would need to store them as a DateTime column.

Casting the varchar values to DateTimes is very simple.

select CAST('Nov 14 2016 2:42PM' as DATETIME), 
       CAST('2014-03-17T10:38:13.300' as DATETIME)

returns

  2016-11-14 14:42:00.000   and    2014-03-17 10:38:13.300

If you want to update all of the rows in the table you would use this syntax.

UPDATE [TableName]
SET [ColumnName] = CAST([ColumnName] as DATETIME)

You could then alter the table and change the column type from Varchar to DateTime. Though you then may need to modify any code that accesses this table column and expects a varchar.

How do you update a DateTime field in T-SQL?

When in doubt, be explicit about the data type conversion using CAST/CONVERT:

UPDATE TABLE
   SET EndDate = CAST('2009-05-25' AS DATETIME)
 WHERE Id = 1

Update the date in the database with +1 month

You never told us the database you are using, so here are several answers:

MySQL:

UPDATE users
SET date = DATE_ADD(date, INTERVAL 1 month )

SQL Server:

UPDATE users
SET date = DATEADD(MONTH, 1, date)

Oracle:

UPDATE users
SET date = ADD_MONTHS(date, 1)

How to change only the year of a date datatype

If all rows need to be decreased by two years, then:

UPDATE dbo.TableToUpdate
  SET [Date Column] = DATEADD(YEAR, -2, [Date Column]);

If all rows need to be set to a specific year (say, 2019), and the column is date:

UPDATE dbo.TableToUpdate
  SET [Date Column] = DATEFROMPARTS(2019, MONTH([Date Column]), DAY([Date Column]);

If all rows need to be set to a specific year (say, 2019) and the column is not date, you could use DATETIMEFROMPARTS or SMALLDATETIMEFROMPARTS, but at that point the following becomes shorter:

UPDATE dbo.TableToUpdate
  SET [Date Column] = DATEADD
  (
    YEAR, 
    -DATEDIFF(YEAR, '20190101', [Date Column]), 
    [Date Column]
  );

---

Related Topics