March 8, 2017 at 11:12 am
I have business logic for date comparisons that go beyond the typical DATEDIFF functionality. For example, when determining the number of years between two dates:declare @DateTest table (FirstDate date, SecondDate date, ExpectedResult smallint);
insert into @DateTest (FirstDate, SecondDate, ExpectedResult) values
('2012-03-10', '2013-02-18', 0), ('2012-03-10', '2013-03-10', 1), ('2012-03-10', '2013-03-20', 1), ('2012-02-29', '2014-02-28', 1), ('2012-03-10', '2013-08-15', 1), ('2012-02-29', '2014-03-01', 2),
('2013-02-18', '2012-03-10', 0), ('2013-03-10', '2012-03-10', 1), ('2013-03-20', '2012-03-10', 1), ('2014-02-28', '2012-02-29', 1), ('2013-08-15', '2012-03-10', 1), ('2014-03-01', '2012-02-29', 2);
SELECT FirstDate, SecondDate, ExpectedResult,
ABS(DATEDIFF(year, FirstDate, SecondDate)) as CurrentMethod,
CASE WHEN SecondDate >= FirstDate THEN
CASE WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) < DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) < DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate) -1)
WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) >= DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) > DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate)) END
ELSE CASE WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) < DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) < DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate) -1)
WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) >= DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) > DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate)) END
END AS NewMethod
FROM @DateTest;
As the complex CASE statement is executed in multiple places, the natural thing to do is to place this into a user-defined function. However, we all know how such scalar UDFs affect performance by turning set-based logic into RBAR operations. Another thought I had was to encapsulate the logic in a table-valued UDF or even a view except that the selection of the records that produce both FirstDate and SecondDate are two different tables that are joined by logic unique to each query that needs the date comparison.
Does someone knows how to create non-scalar, deterministic functions like DATEDIFF? I appreciate any insight that may help me from replicating this quite ugly code throughout my system.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 8, 2017 at 1:04 pm
how about this:SELECT CAST(ABS(CAST(CAST(FirstDate AS DATETIME) AS INT) - CAST(CAST(SecondDate AS DATETIME) AS INT)) AS INT)/365
Not positive that outer cast to an INT is required, but that'll remove all decimals on the division so you should always get an integer as your result.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 8, 2017 at 1:52 pm
Aaron N. Cutshall - Wednesday, March 8, 2017 11:12 AMI have business logic for date comparisons that go beyond the typical DATEDIFF functionality. For example, when determining the number of years between two dates:
declare @DateTest table (FirstDate date, SecondDate date, ExpectedResult smallint);
insert into @DateTest (FirstDate, SecondDate, ExpectedResult) values
('2012-03-10', '2013-02-18', 0), ('2012-03-10', '2013-03-10', 1), ('2012-03-10', '2013-03-20', 1), ('2012-02-29', '2014-02-28', 1), ('2012-03-10', '2013-08-15', 1), ('2012-02-29', '2014-03-01', 2),
('2013-02-18', '2012-03-10', 0), ('2013-03-10', '2012-03-10', 1), ('2013-03-20', '2012-03-10', 1), ('2014-02-28', '2012-02-29', 1), ('2013-08-15', '2012-03-10', 1), ('2014-03-01', '2012-02-29', 2);SELECT FirstDate, SecondDate, ExpectedResult,
ABS(DATEDIFF(year, FirstDate, SecondDate)) as CurrentMethod,
CASE WHEN SecondDate >= FirstDate THEN
CASE WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) < DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) < DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate) -1)
WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) >= DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) > DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate)) END
ELSE CASE WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) < DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) < DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate) -1)
WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) >= DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) > DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate)) END
END AS NewMethod
FROM @DateTest;As the complex CASE statement is executed in multiple places, the natural thing to do is to place this into a user-defined function. However, we all know how such scalar UDFs affect performance by turning set-based logic into RBAR operations. Another thought I had was to encapsulate the logic in a table-valued UDF or even a view except that the selection of the records that produce both FirstDate and SecondDate are two different tables that are joined by logic unique to each query that needs the date comparison.
Does someone knows how to create non-scalar, deterministic functions like DATEDIFF? I appreciate any insight that may help me from replicating this quite ugly code throughout my system.
Try this Aaron:
Oh crap! Copying and pasting doesn't work with mobiles. Hang on.
Tweak this for [years], or holler if you're having trouble:
https://www.sqlservercentral.com/Forums/FindPost1852032.aspx
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 8, 2017 at 2:09 pm
bmg002 - Wednesday, March 8, 2017 1:04 PMhow about this:SELECT CAST(ABS(CAST(CAST(FirstDate AS DATETIME) AS INT) - CAST(CAST(SecondDate AS DATETIME) AS INT)) AS INT)/365
Not positive that outer cast to an INT is required, but that'll remove all decimals on the division so you should always get an integer as your result.
Very interesting!! With a couple of slight mods, I got the following to work:declare @DateTest table (FirstDate datetime, SecondDate datetime, ExpectedResult smallint, Description varchar(128));
insert into @DateTest (FirstDate, SecondDate, ExpectedResult, Description) values
('2012-03-10', '2013-02-18', 0, 'Almost a year'),
('2012-03-10', '2013-03-10', 1, 'Exactly a year'),
('2012-03-10', '2013-03-20', 1, '1 year + 10 days'),
('2012-02-29', '2014-02-28', 1, '2 years from leap day-1'),
('2012-03-10', '2013-08-15', 1, '1 year + several months'),
('2012-02-29', '2014-03-01', 2, '2 years from leap day+1'),
('2013-02-18', '2012-03-10', 0, 'Rev: Almost a year'),
('2013-03-10', '2012-03-10', 1, 'Rev: Exactly a year'),
('2013-03-20', '2012-03-10', 1, 'Rev: 1 year + 10 days'),
('2014-02-28', '2012-02-29', 1, 'Rev: 2 years from leap day-1'),
('2013-08-15', '2012-03-10', 1, 'Rev: 1 year + several months'),
('2014-03-01', '2012-02-29', 2, 'Rev: 2 years from leap day+1');
SELECT Description, FirstDate, SecondDate, ExpectedResult,
ABS(DATEDIFF(year, FirstDate, SecondDate)) as CurrentMethod,
CASE WHEN SecondDate >= FirstDate THEN
CASE WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) < DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) < DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate) -1)
WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) >= DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) > DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate)) END
ELSE CASE WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) < DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) < DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate) -1)
WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) >= DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) > DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate)) END
END AS NewMethod,
CAST(ABS(CAST(FirstDate AS INT) - CAST(SecondDate AS INT))/365.25 + .001 AS INT) AS SimpleMethod
FROM @DateTest;
First of all, my dates are in datetime format already -- just wasn't for my example. Second, I made a slight modification to adjust for leap year which dividing by 365 doesn't account for plus a slight rounding adjustment.
While this doesn't answer my original question about encapsulating a calculation like this, it does solve this particular problem more elegantly than what I had. I'm still interested in the encapsulation issue for other circumstances.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 8, 2017 at 2:39 pm
I think to encapsulate it you would need to put it into a UDF but as you said that'd be a RBAR thing and you are trying to avoid those.
I am not sure of a good solution to encapsulating it. If it was me, I'd build it into a view or if the data from first date and second date don't change all that often, throw it into a table to pull the data from and update the table nightly (or hourly) from a SQL Job? The problem with updating the table is that you will get blocking during that time, but if the dates don't change all that often, a nightly refresh of the dates may be sufficient or to have a trigger on the table so when it gets updated, the result table could get updated. a single row being updated should be pretty quick for having 3-4 columns, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 8, 2017 at 2:47 pm
bmg002 - Wednesday, March 8, 2017 2:39 PMI think to encapsulate it you would need to put it into a UDF but as you said that'd be a RBAR thing and you are trying to avoid those.I am not sure of a good solution to encapsulating it. If it was me, I'd build it into a view or if the data from first date and second date don't change all that often, throw it into a table to pull the data from and update the table nightly (or hourly) from a SQL Job? The problem with updating the table is that you will get blocking during that time, but if the dates don't change all that often, a nightly refresh of the dates may be sufficient or to have a trigger on the table so when it gets updated, the result table could get updated. a single row being updated should be pretty quick for having 3-4 columns, no?
If the dates were from a single table, I'd introduce a computed column. Unfortunately, I don't know the anticipated relationship between the two tables ahead of time. But, in any case, your calculation is far simpler and I'll look to implement it as soon as I can confirm it fits all situations (which I think I will) as outlined by the business logic.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 8, 2017 at 2:59 pm
Aaron N. Cutshall - Wednesday, March 8, 2017 2:47 PMbmg002 - Wednesday, March 8, 2017 2:39 PMI think to encapsulate it you would need to put it into a UDF but as you said that'd be a RBAR thing and you are trying to avoid those.I am not sure of a good solution to encapsulating it. If it was me, I'd build it into a view or if the data from first date and second date don't change all that often, throw it into a table to pull the data from and update the table nightly (or hourly) from a SQL Job? The problem with updating the table is that you will get blocking during that time, but if the dates don't change all that often, a nightly refresh of the dates may be sufficient or to have a trigger on the table so when it gets updated, the result table could get updated. a single row being updated should be pretty quick for having 3-4 columns, no?
If the dates were from a single table, I'd introduce a computed column. Unfortunately, I don't know the anticipated relationship between the two tables ahead of time. But, in any case, your calculation is far simpler and I'll look to implement it as soon as I can confirm it fits all situations (which I think I will) as outlined by the business logic.
Regardless of how simple the calculation is you still have the same issue if you're trying to use it in where clauses which you were originally worried about, the value of putting it in a UDF would be that the logic only exists in one place.
Theoretically you could create some kind of double day dimension type table with the values for a cartesian join of two dates with the calculation already done, although that would grow pretty huge without restricting the dates quite a lot 🙂
March 8, 2017 at 3:07 pm
While this doesn't answer my original question about encapsulating a calculation like this, it does solve this particular problem more elegantly than what I had. I'm still interested in the encapsulation issue for other circumstances.
Just posting in hopes the thread produces new insights. I've honestly written it off as undoable myself but that's because I'm probably not familiar with all the different paradigms available with SQL. I know I've used m4 (the macro processor) for creating constructs that would otherwise be crazy repetitive, but then SQL Server is no longer the master repository for your code, and that's also in the belief that T-SQL penalizes DRY because of all the other things it tries to do. If its just moderately repetitive, might want to include comments that refer to a "master copy" of the segment somewhere so folks will know to change the rules wherever they need changed.
March 9, 2017 at 9:21 am
Just curious as to why a DATEDIFF(year, FirstDate, SecondDate) doesn't work for you?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 9, 2017 at 9:41 am
sgmunson - Thursday, March 9, 2017 9:21 AMJust curious as to why a DATEDIFF(year, FirstDate, SecondDate) doesn't work for you?
If you run the sample code provided, you'll see why: it does not produce the desired results 😉
Presumably because DATEDIFF counts boundaries rather than time differences. How many of us really want to see 1 returned as the result of the following, for example?
SELECT DATEDIFF(YEAR,'20161231','20170101')
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 9, 2017 at 9:55 am
Your code works fine as an iTVF:
DROP FUNCTION IF EXISTS dbo.FancyDateDiff;
go
CREATE FUNCTION dbo.FancyDateDiff
( @FirstDate datetime, @SecondDate datetime)
RETURNS TABLE
RETURN
SELECT NewMethod = (CASE WHEN @SecondDate >= @FirstDate THEN
CASE WHEN (DATEPART(MONTH, @SecondDate) = DATEPART(MONTH, @FirstDate) AND DATEPART(DAY, @SecondDate) < DATEPART(DAY, @FirstDate))
OR (DATEPART(MONTH, @SecondDate) < DATEPART(MONTH, @FirstDate))
THEN ABS(DATEPART(YEAR, @SecondDate) - DATEPART(YEAR, @FirstDate) -1)
WHEN (DATEPART(MONTH, @SecondDate) = DATEPART(MONTH, @FirstDate) AND DATEPART(DAY, @SecondDate) >= DATEPART(DAY, @FirstDate))
OR (DATEPART(MONTH, @SecondDate) > DATEPART(MONTH, @FirstDate))
THEN ABS(DATEPART(YEAR, @SecondDate) - DATEPART(YEAR, @FirstDate)) END
ELSE CASE WHEN (DATEPART(MONTH, @FirstDate) = DATEPART(MONTH, @SecondDate) AND DATEPART(DAY, @FirstDate) < DATEPART(DAY, @SecondDate))
OR (DATEPART(MONTH, @FirstDate) < DATEPART(MONTH, @SecondDate))
THEN ABS(DATEPART(YEAR, @FirstDate) - DATEPART(YEAR, @SecondDate) -1)
WHEN (DATEPART(MONTH, @FirstDate) = DATEPART(MONTH, @SecondDate) AND DATEPART(DAY, @FirstDate) >= DATEPART(DAY, @SecondDate))
OR (DATEPART(MONTH, @FirstDate) > DATEPART(MONTH, @SecondDate))
THEN ABS(DATEPART(YEAR, @FirstDate) - DATEPART(YEAR, @SecondDate)) END
END)
go
DECLARE @DateTest table (FirstDate datetime, SecondDate datetime);
INSERT @DateTest (FirstDate, SecondDate) VALUES
('2012-03-10', '2013-02-18'),
('2012-03-10', '2013-03-10'),
('2012-03-10', '2013-03-20'),
('2012-02-29', '2014-02-28'),
('2012-03-10', '2013-08-15'),
('2012-02-29', '2014-03-01'),
('2013-02-18', '2012-03-10'),
('2013-03-10', '2012-03-10'),
('2013-03-20', '2012-03-10'),
('2014-02-28', '2012-02-29'),
('2013-08-15', '2012-03-10'),
('2014-03-01', '2012-02-29');
SELECT
dt.*
, fdd.NewMethod
FROM
@DateTest dt
CROSS APPLY dbo.FancyDateDiff(dt.FirstDate, dt.SecondDate) fdd;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 9, 2017 at 10:12 am
Something like this?
I included an option with an inline table-valued function and one direct option. The function won't have a performance impact.
CREATE FUNCTION dbo.YearsDifference( @Date1 date, @Date2 date)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT DATEDIFF(year, FirstDate, SecondDate)
+ CASE WHEN RIGHT( FirstDate, 5) > RIGHT( SecondDate, 5) THEN -1 ELSE 0 END AS Years
FROM (SELECT CASE WHEN @Date1 < @Date2 THEN @Date1 ELSE @Date2 END AS FirstDate,
CASE WHEN @Date1 < @Date2 THEN @Date2 ELSE @Date1 END AS SecondDate)x
GO
declare @DateTest table (FirstDate date, SecondDate date, ExpectedResult smallint);
insert into @DateTest (FirstDate, SecondDate, ExpectedResult) values
('2012-03-10', '2013-02-18', 0), ('2012-03-10', '2013-03-10', 1), ('2012-03-10', '2013-03-20', 1), ('2012-02-29', '2014-02-28', 1), ('2012-03-10', '2013-08-15', 1), ('2012-02-29', '2014-03-01', 2),
('2013-02-18', '2012-03-10', 0), ('2013-03-10', '2012-03-10', 1), ('2013-03-20', '2012-03-10', 1), ('2014-02-28', '2012-02-29', 1), ('2013-08-15', '2012-03-10', 1), ('2014-03-01', '2012-02-29', 2);
SELECT t.FirstDate, t.SecondDate, ExpectedResult,
CASE WHEN t.SecondDate >= t.FirstDate
THEN DATEDIFF(year, t.FirstDate, t.SecondDate) + CASE WHEN RIGHT( CONVERT(CHAR(8), t.FirstDate, 112),4) > RIGHT( CONVERT(CHAR(8), t.SecondDate, 112),4) THEN -1 ELSE 0 END
ELSE DATEDIFF(year, t.SecondDate, t.FirstDate) + CASE WHEN RIGHT( CONVERT(CHAR(8), t.SecondDate, 112),4) > RIGHT( CONVERT(CHAR(8), t.FirstDate, 112),4) THEN -1 ELSE 0 END
END, y.*
FROM @DateTest t
CROSS APPLY dbo.YearsDifference(FirstDate, SecondDate)y;
GO
DROP FUNCTION YearsDifference
March 9, 2017 at 10:35 am
Or this:
SELECT FirstDate, SecondDate, ExpectedResult,
ABS(DATEDIFF(year, FirstDate, SecondDate)) as CurrentMethod,
CASE WHEN SecondDate >= FirstDate THEN
CASE WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) < DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) < DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate) -1)
WHEN (DATEPART(MONTH, SecondDate) = DATEPART(MONTH, FirstDate) AND DATEPART(DAY, SecondDate) >= DATEPART(DAY, FirstDate))
OR (DATEPART(MONTH, SecondDate) > DATEPART(MONTH, FirstDate))
THEN ABS(DATEPART(YEAR, SecondDate) - DATEPART(YEAR, FirstDate)) END
ELSE CASE WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) < DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) < DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate) -1)
WHEN (DATEPART(MONTH, FirstDate) = DATEPART(MONTH, SecondDate) AND DATEPART(DAY, FirstDate) >= DATEPART(DAY, SecondDate))
OR (DATEPART(MONTH, FirstDate) > DATEPART(MONTH, SecondDate))
THEN ABS(DATEPART(YEAR, FirstDate) - DATEPART(YEAR, SecondDate)) END
END AS NewMethod,
DATEDIFF(YEAR, LowDate, HighDate) - CASE WHEN CONVERT(varchar(5), LowDate, 1) > CONVERT(varchar(5), HighDate, 1) THEN 1 ELSE 0 END AS ScottMethod
FROM @DateTest
CROSS APPLY (
SELECT CASE WHEN SecondDate >= FirstDate THEN SecondDate ELSE FirstDate END AS HighDate,
CASE WHEN SecondDate >= FirstDate THEN FirstDate ELSE SecondDate END AS LowDate
) AS assign_alias_names1
;
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 9, 2017 at 12:14 pm
Phil Parkin - Thursday, March 9, 2017 9:55 AMYour code works fine as an iTVF:
DROP FUNCTION IF EXISTS dbo.FancyDateDiff;
goCREATE FUNCTION dbo.FancyDateDiff
( @FirstDate datetime, @SecondDate datetime)
RETURNS TABLE
RETURN
SELECT NewMethod = (CASE WHEN @SecondDate >= @FirstDate THEN
CASE WHEN (DATEPART(MONTH, @SecondDate) = DATEPART(MONTH, @FirstDate) AND DATEPART(DAY, @SecondDate) < DATEPART(DAY, @FirstDate))
OR (DATEPART(MONTH, @SecondDate) < DATEPART(MONTH, @FirstDate))
THEN ABS(DATEPART(YEAR, @SecondDate) - DATEPART(YEAR, @FirstDate) -1)
WHEN (DATEPART(MONTH, @SecondDate) = DATEPART(MONTH, @FirstDate) AND DATEPART(DAY, @SecondDate) >= DATEPART(DAY, @FirstDate))
OR (DATEPART(MONTH, @SecondDate) > DATEPART(MONTH, @FirstDate))
THEN ABS(DATEPART(YEAR, @SecondDate) - DATEPART(YEAR, @FirstDate)) END
ELSE CASE WHEN (DATEPART(MONTH, @FirstDate) = DATEPART(MONTH, @SecondDate) AND DATEPART(DAY, @FirstDate) < DATEPART(DAY, @SecondDate))
OR (DATEPART(MONTH, @FirstDate) < DATEPART(MONTH, @SecondDate))
THEN ABS(DATEPART(YEAR, @FirstDate) - DATEPART(YEAR, @SecondDate) -1)
WHEN (DATEPART(MONTH, @FirstDate) = DATEPART(MONTH, @SecondDate) AND DATEPART(DAY, @FirstDate) >= DATEPART(DAY, @SecondDate))
OR (DATEPART(MONTH, @FirstDate) > DATEPART(MONTH, @SecondDate))
THEN ABS(DATEPART(YEAR, @FirstDate) - DATEPART(YEAR, @SecondDate)) END
END)go
DECLARE @DateTest table (FirstDate datetime, SecondDate datetime);
INSERT @DateTest (FirstDate, SecondDate) VALUES
('2012-03-10', '2013-02-18'),
('2012-03-10', '2013-03-10'),
('2012-03-10', '2013-03-20'),
('2012-02-29', '2014-02-28'),
('2012-03-10', '2013-08-15'),
('2012-02-29', '2014-03-01'),
('2013-02-18', '2012-03-10'),
('2013-03-10', '2012-03-10'),
('2013-03-20', '2012-03-10'),
('2014-02-28', '2012-02-29'),
('2013-08-15', '2012-03-10'),
('2014-03-01', '2012-02-29');SELECT
dt.*
, fdd.NewMethod
FROM
@DateTest dt
CROSS APPLY dbo.FancyDateDiff(dt.FirstDate, dt.SecondDate) fdd;
Phil,
This is intriguing, but doesn't this present the same issue as a scalar UDF wherein it results in a single record and has to be executed for each change in parameters?
Aaron
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
March 9, 2017 at 12:30 pm
Aaron N. Cutshall - Thursday, March 9, 2017 12:14 PMPhil,
This is intriguing, but doesn't this present the same issue as a scalar UDF wherein it results in a single record and has to be executed for each change in parameters?
Aaron
No,
This will add the same overhead as using a view, which is practically none. ITVF integrate into the query and are resolved as a set, in this case, it's just as adding the calculation which would add a Compute Scalar operator. Here's an article on this subject: http://www.sqlservercentral.com/articles/T-SQL/91724/
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply