Monday, April 14, 2014

T-SQL Tips: Swap The Year Value of a Date

Here's a quick T-SQL tip for you.
Suppose you want to swap the year part of a date value in SQL server. Here's a quick and simple way in which you can do it.
Lets consider the below example

DECLARE @DateValue datetime ,@SwapYear int
SELECT @DateValue = '20140122',@SwapYear = 2000

So in the above case the requirement is to swap year value of datevalue which is 2014 with the passed year value ie 2000
This can be done using the below query

SELECT DATEADD(yy,@SwapYear - YEAR(@DateValue),@DateValue)  AS YearSwappedDate

Run this in SQL management studio and you can see result as below


Which clearly indicates that query returns date with year value swapped.
This is particularly useful when we want to determine birth date of a person on a year given the date of birth. Hope you find this tip useful.
Do let me know any comments/clarification you've on the above.