June 14, 2016 at 10:36 am
Hi guys,
I'm from a vba background and I'm looking to use an SQL function to convert a datetime value into a harvest season year. The harvest season runs from the beginning of July to the end of June the following year. So for example 2016-06-15 10:12:00 would return 2015, 2016-07-15 10:12:00 would return 2016 and 2017-03-12 14:30:00 should return 2016.
In VBA I'd use something like this...
Function GetHarvestYear(DateTimeInput As Variant) As Integer
Dim intHarvestMonth As Integer
intHarvestMonth = Month(DateTimeInput)
Select Case intHarvestMonth
Case 7 To 12
GetHarvestYear = Year(DateTimeInput)
Case Else
GetHarvestYear = Year(DateTimeInput) - 1
End Select
End Function
Can someone give me a starter on how to do this as an SQL function.
Thank you!
June 14, 2016 at 11:06 am
something like this:
declare @TestDate datetime;
set @TestDate = '2016-06-15 10:12:00';
select year(dateadd(year,datediff(year,0,dateadd(month,-6,@TestDate)),0));
set @TestDate = '2016-07-15 10:12:00';
select year(dateadd(year,datediff(year,0,dateadd(month,-6,@TestDate)),0));
set @TestDate = '2017-03-12 14:30:00';
select year(dateadd(year,datediff(year,0,dateadd(month,-6,@TestDate)),0));
June 14, 2016 at 11:06 am
I wouldn't do this in an SQL function. I'd put it as a column in a Calendar table and have it pre-calculated and queriable.
Use Calendar tables for this kind of thing. It's faster that doing the math at runtime, since you only calculate it once, not once per query-row.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2016 at 11:10 am
But do NOT DO THIS AS A SCALAR UDF IN SQL SERVER!!! They are HORRIBLY BAD!!!!!!!!!
If you cannot simply inline the code into your TSQL (best), then do this as an INLINE TABLE VALUED FUNCTION and use CROSS/OUTER APPLY.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 14, 2016 at 11:10 am
GSquared (6/14/2016)
I wouldn't do this in an SQL function. I'd put it as a column in a Calendar table and have it pre-calculated and queriable.Use Calendar tables for this kind of thing. It's faster that doing the math at runtime, since you only calculate it once, not once per query-row.
If I were to write an actual function I'd write it as an iTVF and call it in a CROSS APPLY in the FROM clause.
June 14, 2016 at 11:26 am
I expect that Lynn's option can be simplified without errors.
DECLARE @Sample TABLE(
SomeDate datetime,
HarvestYr int
);
INSERT INTO @Sample
VALUES
('2016-06-15T10:12:00', 2015),
('2016-07-15T10:12:00', 2016),
('2017-03-12T14:30:00', 2016);
SELECT *, YEAR( DATEADD( MM, -6, SomeDate))
FROM @Sample;
June 14, 2016 at 11:45 am
Luis Cazares (6/14/2016)
I expect that Lynn's option can be simplified without errors.
DECLARE @Sample TABLE(
SomeDate datetime,
HarvestYr int
);
INSERT INTO @Sample
VALUES
('2016-06-15T10:12:00', 2015),
('2016-07-15T10:12:00', 2016),
('2017-03-12T14:30:00', 2016);
SELECT *, YEAR( DATEADD( MM, -6, SomeDate))
FROM @Sample;
Simplification is always welcome.
June 14, 2016 at 1:03 pm
Thank you so much for the replies this is what I ended up doing as a Scalar-Valued Function
CREATE FUNCTION [dbo].[GetHarvestYear] (@InputDate datetime)
RETURNS int
AS
BEGIN
DECLARE @ret int;
SELECT @ret = year(dateadd(year,datediff(year,0,dateadd(month,-6,@InputDate)),0));
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
and I call it like this ...
SELECT GetHarvestYear([ArrivalDateTime]) as HarvestYear
FROM [tblFuelFlow]
Your help was greatly appreciated!
June 14, 2016 at 1:15 pm
joe-584802 (6/14/2016)
Thank you so much for the replies this is what I ended up doing as a Scalar-Valued Function
CREATE FUNCTION [dbo].[GetHarvestYear] (@InputDate datetime)RETURNS int
AS
BEGIN
DECLARE @ret int;
SELECT @ret = year(dateadd(year,datediff(year,0,dateadd(month,-6,@InputDate)),0));
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
and I call it like this ...
SELECT GetHarvestYear([ArrivalDateTime]) as HarvestYearFROM [tblFuelFlow]
Your help was greatly appreciated!
WOW. So even with the twelve or so exclamation points I put in my statements about Scalar UDFs being HORRIBLY BAD (plus another poster saying to not use them) you STILL went with an sUDF?!?
Please see if you can find a copy of the SQL Server MVP Deep Dives 2 book and read my chapter there entitled "Death by UDF".
Oh, and I note that your initial post didn't say anything about handling invalid data and returning a 0 for the YEAR if that was encountered.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 14, 2016 at 1:16 pm
joe-584802 (6/14/2016)
Thank you so much for the replies this is what I ended up doing as a Scalar-Valued Function
CREATE FUNCTION [dbo].[GetHarvestYear] (@InputDate datetime)RETURNS int
AS
BEGIN
DECLARE @ret int;
SELECT @ret = year(dateadd(year,datediff(year,0,dateadd(month,-6,@InputDate)),0));
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
and I call it like this ...
SELECT GetHarvestYear([ArrivalDateTime]) as HarvestYearFROM [tblFuelFlow]
Your help was greatly appreciated!
Better solution:
create function dbo.Get_HarvestYear(@InputDate datetime)
returns table with schemabinding return
select year(dateadd(month, -6, @InputDate)) HarvestYear
go
Usage:
SELECT
HarvestYear
FROM
[tblFuelFlow]
cross apply (select HarvestYear from dbo.Get_HarvestYear([ArrivalDateTime]);
June 14, 2016 at 1:20 pm
Lynn Pettis (6/14/2016)
joe-584802 (6/14/2016)
Thank you so much for the replies this is what I ended up doing as a Scalar-Valued Function
CREATE FUNCTION [dbo].[GetHarvestYear] (@InputDate datetime)RETURNS int
AS
BEGIN
DECLARE @ret int;
SELECT @ret = year(dateadd(year,datediff(year,0,dateadd(month,-6,@InputDate)),0));
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
and I call it like this ...
SELECT GetHarvestYear([ArrivalDateTime]) as HarvestYearFROM [tblFuelFlow]
Your help was greatly appreciated!
Better solution:
create function dbo.Get_HarvestYear(@InputDate datetime)
returns table with schemabinding return
select year(dateadd(month, -6, @InputDate)) HarvestYear
go
Usage:
SELECT
HarvestYear
FROM
[tblFuelFlow]
cross apply (select HarvestYear from dbo.Get_HarvestYear([ArrivalDateTime]);
Usage, modified to handle null:
SELECT
isnull(HarvestYear,0) HarvestYear
FROM
[tblFuelFlow]
outer apply (select HarvestYear from dbo.Get_HarvestYear([ArrivalDateTime]);
June 14, 2016 at 1:26 pm
Avoid that. User defined scalar functions in SQL Server are known for giving performance problems. They're slow and they prevent parallelism.
EDIT: Took too long to reply, I just want to make clear that the comment is intended for joe (OP)
June 14, 2016 at 1:33 pm
Thanks for the improvement Lynne, I'm guessing table valued functions are more efficient than in-line scalar valued functions?
June 14, 2016 at 1:38 pm
I just tested a Calendar table version against the UDFs defined here.
Calendar table was consistently about 10X faster.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2016 at 1:44 pm
Hi Kevin, I didn't realise that there was such an issue in using a scalar function, the database that I'm using is not using huge record numbers so I thought it was a more simplistic approach. I thought to handle the null entry as an afterthought so I could flag it for another purpose. I do take all the comments on board and Lynne came back with an improved function that uses your suggestion.
Appreciate your great advice and pointers, I do take all the comments on-board!!
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply