Compare 2 column dates

Transact-SQL
,
1

I need to set a column that is in date format the same year (only) as a different column. I need to leave the month and day the same, but only change the year. I am at a loss as to the syntax and if I should be using DATEDIFF or DATEPART..can anyone help? Basically I need to do this:
Update column A to the same year as column B without changing the month or day.

2

Before you do the update, check if you get the results you expect

select colA
      ,colB
      ,dateadd(year,datediff(year,colA,colB),colA) as newA
  from yourtable
;

update yourtable
   set colA=dateadd(year,datediff(year,colA,colB),colA)
;
1 Like
3

You can use the CAST to the new DATE data type in SQL Server 2008 to compare just the date portion:

IF CAST(DateField1 AS DATE) = CAST(DateField2 AS DATE)

Please move on this to know more how to compare the 2 columns dates https://blog.udemy.com/sql-compare-dates/

4

:slight_smile:

In case helpful we lay out our code to make sure that we exactly reuse the code between the Test and the Actual update as follows

-- UPDATE U SET
-- SELECT colA, colB, 'New values>>',
     colA=dateadd(year,datediff(year,colA,colB),colA)
FROM YourTable AS U

and then we comment-in the SELECT first, for testing, and then change that to comment-in the UPDATE once the test is conclusive.

Can also be worth using a transaction:

BEGIN TRANSACTION
-- UPDATE U SET
-- SELECT colA, colB, 'New values>>',
     colA=dateadd(year,datediff(year,colA,colB),colA)
FROM YourTable AS U
-- COMMIT
-- ROLLBACK

and then using the ROLLBACK line rather than the COMMIT line IF the UPDATE returns a different number of rows to the SELECT, or a Trigger produces an error / side effect / etc.

1 Like
5

@kristen your advice is gold :thumbsup:

1 Like