T-SQL Tips: UDF To Swap The Parts Of A Datetime Value

Context

There was a requirement recently which required changing only the year portion of one of the date fields.
The business scenario was to extend validity of insurance details on a table.
I've also come across similar requests in forums also asking whether its possible to swap one or more parts of a datetime value without affecting the other parts of the date

Solution

This article provides a solution for scenarios like above.
The logic for these type of scenarios uses date function DATEADD to do an integer arithmetic over datetime values to swap the required parts. Since dates are represented internally as numbers in SQL Server its very easy to do integer operations with date values.

Illustration

Consider the below illustration

declare @date datetime = '20141020 16:35'

Now let the requirement be that we need to swap the year part of the date with 2020. We can do it by taking the offset between current year and new year value and add it to the date value ie 2020-2014

Translating it using DATEADD we can use

SELECT DATEADD(yy,@Year-YEAR(@date),@date) AS YearSwappedDate

Now see the result

As you see the year part gets swapped correctly for the passed value keeping the other parts unchanged

Now lets try for year and month parts together. Going again by the same theory we can use an expression as below

declare @date datetime = '20141020 16:35'
declare @Year int = 2020
declare @Month int = 9
SELECT DATEADD(mm,@Month -MONTH(@date)+ ((@Year-YEAR(@date))*12),@date) AS YearMonthSwappedDate

What we're doing here is take the offset between month and year parts and doing addition with that. I'm reducing year also to month level by multiplying 12 so that we can just use a single DATEADD call to do the addition. The result for this looks like below

Now lets go one more step ahead and do replacement of day part also. Again extending the logic we can write expression as below

declare @date datetime = '20141020 16:35'
declare @Year int = 2020
declare @Month int = 9
declare @Day int = 12

SELECT DATEADD(mm,@Month -MONTH(@date)+ ((@Year-YEAR(@date))*12),@date) + @Day - DAY(@date)

This provides us with the results as below

Generic Function

Lets convert this into a generic UDF. I've also extended it to add the hour minute and second parts.
The UDF would look like below

CREATE Function SwapDateParts
(
@InputDate datetime,
@SwapYear int = NULL,
@SwapMonth int = NULL,
@SwapDay int = NULL,
@SwapHour int = NULL,
@SwapMinute int = NULL,
@SwapSecond int = NULL
)
RETURNS datetime
AS
BEGIN
DECLARE @SwappedDate datetime
SET @SwappedDate = DATEADD(ss,(COALESCE(@SwapHour-DATEPART(HOUR,@InputDate),0)*60*60) + (COALESCE(@SwapMinute-DATEPART(MINUTE,@InputDate),0)*60)+COALESCE(@SwapSecond-DATEPART(SECOND,@InputDate),0),DATEADD(mm,COALESCE(@SwapMonth -MONTH(@InputDate),0)+ (COALESCE((@SwapYear-YEAR(@InputDate))*12,0)),@InputDate)) + COALESCE(@SwapDay - DAY(@InputDate),0)
RETURN @SwappedDate
END

Try running it for different set of inputs and see the result

SELECT dbo.SwapDateParts('20140516 16:33:22',DEFAULT,1,DEFAULT,DEFAULT,DEFAULT,DEFAULT) AS MonthSwapped,
dbo.SwapDateParts('20140516 16:33:22',DEFAULT,6,11,DEFAULT,DEFAULT,DEFAULT) AS MonthDaySwapped,
dbo.SwapDateParts('20140516 16:33:22',1980,12,30,DEFAULT,DEFAULT,DEFAULT) AS YearMonthDaySwapped,
dbo.SwapDateParts('20140516 16:33:22',2054,DEFAULT,30,DEFAULT,56,17) AS YearDayMinuteSecondSwapped

See the output which would be the below

Summary

 This is a very good utility function that can be applied in similar scenarios to swap out one or more parts of a given datetime value