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
