Update only Year, Month or Day in a SQL Server Date

Problem

We have an automated process to load exchange rates into a SQL Server database using a backend process. We found that it loaded almost all of the records perfectly except for a few records, for which it chose some random years when loading the data into the table. The day and month are correct, only the year was different. It is still an unresolved mystery, but the main problem was how to update the dates to the correct year with minimal effort in SQL Server.

Solution

After some research I found that we can use the DATEADD() function to select and replace just the year part from the date column to the correct year.

Let's demonstrate how.

Create Test SQL Server Table and Data

Let's first create a table.

create table Test 
(
   ID int,
   start_date datetime Not Null,
   end_date datetime Not Null
)

Insert some dummy data into the table as shown below.

insert into test (id, start_date, end_date) values (1,'9/1/2019','9/30/2019')
insert into test (id, start_date, end_date) values (2,'9/1/2019','9/30/2019')
insert into test (id, start_date, end_date) values (3,'1/1/2019','1/31/2019')
insert into test (id, start_date, end_date) values (4,'1/1/2019','1/31/2019')
insert into test (id, start_date, end_date) values (5,'2/1/2012','2/28/2019')
insert into test (id, start_date, end_date) values (6,'2/1/2012','2/28/2019')
insert into test (id, start_date, end_date) values (7,'3/1/2005','3/31/2019')
insert into test (id, start_date, end_date) values (8,'3/1/2005','3/31/2019')
insert into test (id, start_date, end_date) values (9,'4/1/1998','4/30/2019')
insert into test (id, start_date, end_date) values (10,'4/1/1998','4/30/2019')
insert into test (id, start_date, end_date) values (11,'5/1/1991','5/31/2019')
insert into test (id, start_date, end_date) values (12,'5/1/1991','5/31/2019')
insert into test (id, start_date, end_date) values (13,'6/1/1984','6/30/2019')
insert into test (id, start_date, end_date) values (14,'6/1/1984','6/30/2019')
insert into test (id, start_date, end_date) values (15,'7/1/1977','7/31/2019')
insert into test (id, start_date, end_date) values (16,'7/1/1977','7/31/2019')
insert into test (id, start_date, end_date) values (17,'8/1/1970','8/31/2019')
insert into test (id, start_date, end_date) values (18,'8/1/1970','8/31/2019')

After you insert the records into table, it should look as below:

As you can see, the end_date shows the last day of the month and the start_date shows the first day of the month.  If you look closely, the year is not the current year. The year for rows 5 through row 18 should be 2019.

Update only the YEAR part of a SQL Server date using the DATEADD() function

Let's use the DATEADD() function to update the year from the start_date to a different year.

Use the below query to see if we are getting the desired results. We are replacing the year portion of the date with “2019”.

select 
   dateadd(year, (2019 - year(start_date)), start_date) as start_date_modified, 
   start_date, 
   end_date 
from test

After you run the above query the output should look like below.

If you look closely you will see the difference as highlighted. The start_date_modified column has the current year (2019) whereas the start_date which is the actual stored value in the table shows different years. Now we are able to get the desired result from the SELECT statement.

The next step is to modify the start_date column with the current year.

Use the below script to modify the year part of start_date column to 2019.

update test
set start_date = dateadd(year, (2019 - year(start_date)), start_date)
where ID between 5 and 18

Make sure you use a WHERE clause to ensure you are not updating the entire table and only the desired columns are getting updated. Also, you might also want to create a table backup before you update the records. The backup will help you revert the changes if the result is not as expected. You could also run the update in a transaction and rollback if needed.

After running the UPDATE statement successfully, you will see start_date column is updated as expected and you will see start_date value shows the correct year as shown below:

This is the quickest and easy way to address such situations. You can use the DATEADD() function for other uses like modifying the day or month from date column if required as we will show below.

Update only the DAY part of a SQL Server date using the DATEADD() function

As we can see in the above screenshot, the start_date is the first day of the month. Let’s assume you are supposed change the start_date to the 2^nd day of the month instead of first day of the month. We can use the DATEADD() function to achieve this as we did for the year part of start_date column.

Use the below script to change only the day part of the start_date. Also, we will just update certain rows again.

select 
   dateadd(day, (02 - day(start_date)), start_date) as start_date_modified, 
   start_date, 
   end_date           
from test
where ID between 5 and 18

You can see that start_date_modified has a start date as the 2^nd day of the month instead of the 1^st day of the month as shown below.

As you can see, you can use a function to change the day of the month to any specific day you want.

You can use below UPDATE statement to update the day of the month in the start_date column.

Caution: Make sure you use an appropriate WHERE clause before you run the UPDATE statement and ensure you take a table backup before you make any changes, it will help you to rollback changes to previous state in case of any accidental updates.

update test
set start_date = dateadd(day, (02 - day(start_date)), start_date)
where ID between 5 and 18

Update only the MONTH part of a SQL Server date using the DATEADD() function

Similarly, if you have been asked to change the month of the start_date column to specific month for few records of the table based on some condition, then you can use the dateadd() function to do the same.

Use below script to change the only day part of the start_date.

select dateadd(month, (10 - month(start_date)), start_date) start_date_modified, start_date, end_date
from test
where ID between 5 and 18

After you run above query you will see the output as shown below and you can see for all the rows start_date column month has been changed to 10^th month of the year.

You can use dateadd() function to change the month to any specific month you want. You can use below UPDATE statement to update the month for the start_date column.

Use the below script to change the only month part of the start_date.

update test
set start_date = dateadd(month, (10 - month(start_date)), start_date)
where ID between 5 and 18

Summary

You can use the DATEADD() function to change the day, month, year, quarter, dayofyear, week, weekday, hour, minute, second, milliseconds, microsecond and nanosecond as needed.  For more details you can refer to this article about the DATEADD() function.

Next Steps

• Let me know your feedback or similar situations you faced.
• Check out these related articles:
  • Simplify Date Period Calculations in SQL Server
  • SQL Server Function to return a range of dates

Atul Gaikwad

Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager handling small to large SQL Server production support projects. He is technology savvy and believes in automation and innovation. He likes to automate anything and everything which can help save time and make a DBAs life effortless and smooth, to focus on larger projects. He has extensive hands on experience in SQL Server administration and high availability solutions like clustering, replication, log shipping, mirroring, etc. He also possesses strong knowledge of SSRS and SSIS.

• MSSQLTips Awards: Rookie of the Year Contender – 2016