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


By:   |   Updated: 2019-11-14   |   Comments (1)   |   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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

Nice info.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools