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


By:   |   Updated: 2019-11-14   |   Comments (2)   |   Related: More > Dates

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:

Sample data with invalid year from start_Date column

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.

Date comparision from actual date and modified data using getdate function

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:

Table data with date column after updating with the correct year using getdate()

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 2nd 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 2nd day of the month instead of the 1st day of the month as shown below.

How to update only day of the month from start_Date

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 10th month of the year.

how to update 10th month of the year in start_date

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


Last Updated: 2019-11-14


get scripts

next tip button



About the author
MSSQLTips author 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.

View all my tips





Comments For This Article




Tuesday, December 17, 2019 - 7:12:39 AM - Rob Back To Top (83446)

Thanks for the post :-)

I got a syntax error and had to change the code slightly from this:

dateadd(year, 2019 - year(start_date)), start_date)

to this... (removed the extra bracket):

dateadd(year, 2019 - year(start_date), start_date)

Can I also suggest an alternative for SQL 2012 onwards, which personally I find easier to read as I'm not mathematically minded:

datefromparts('2019', month(start_date), day(start_date)) as start_date_modified

Saturday, November 16, 2019 - 11:14:01 PM - Arun Anbudasan Back To Top (83111)

Nice info.



download


Recommended Reading

Date and Time Conversions Using SQL Server

Format SQL Server Dates with FORMAT Function

Add and Subtract Dates using DATEADD in SQL Server

Creating a date dimension or calendar table in SQL Server

SQL Server Date and Time Functions with Examples





get free sql tips
agree to terms


Learn more about SQL Server tools