SQLTeam.com | Weblogs | Forums

Function to add months parametrized

Hello,
how can I write a function that accept as inputs
a datetime variable, a integer parameter
and returns a datetime that is ahead 0-3-6 months from the input?

For example, if I pass

@StartDateTime = '2019-01-01'
@Split int = 1

get return '2020-01-01'

if
@StartDateTime = '2019-01-01'
@Split int = 2

get return '2020-04-01'

if
@StartDateTime = '2019-01-01'
@Split int = 3

get return '2020-07-01'

Thank you very much.

Luis

The example you provided does not seem to match what you are looking for?

Maybe?:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.your_func_name_here 
(
    @StartDateTime date,
    @Split smallint
)
RETURNS date
AS
BEGIN
RETURN (
    SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + @Split, 0) AS return_date
)
END
GO

--run a test
SELECT test_date, split, dbo.your_func_name_here(test_date, split) AS return_date
FROM ( VALUES('20190101', 1),('20190101', 2),('20190101',3) ) AS test_dates(test_date, split)

1 Like

Correct, and in case I need to obtain 3--6 only moths ahead?
For example:
2019-01-01 - Split 1 --> 2020-01-01
2019-01-01 - Split 2 --> 2019-07-01
2019-01-01 - Split 3 --> 2019-04-01

L.

Correction to use the year of the StartDate you pass in.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
DROP FUNCTION dbo.your_func_name_here
GO
CREATE FUNCTION dbo.your_func_name_here 
(
    @StartDateTime date,
    @Split smallint
)
RETURNS date
AS
BEGIN
RETURN (
    SELECT DATEADD(QUARTER, DATEPART(QUARTER, GETDATE()) - 1 + @Split, start_year) AS return_date
    FROM (
        SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, @StartDateTime), 0) AS start_year
    ) AS calc1
)
END
GO
1 Like

? So the Split you pass in is the quarter#, not the quarters to add??

A 1 in split the date goes forward but a 2 in split the date goes backward??

You need to provide specific rules for how to handle the input values and the output value.

Yes, Split = 1 means annual, so I have to add 1 year.
Split = 2 means semiannual, so I have to add 6 months.
And Split = 3 means quarter, so I have to add 3 months.

L.

Hi

Split 1
Datadd to date months 12

Split 2
Datadd to date 6 months

Split 3
Dateadd to date 3 months

Case statement

why send parameter that has different meaning and is tribal knowledge.
just make the parms be what they mean

send
split 12
split 6
split 3

Because they are business legacy rules, and I cannot change them.

L.

gotcha

declare @Split int = 3, @date datetime = '2019-01-01';

SELECT 
case 
 when @Split = 1 then DATEADD(YEAR,  @Split, @date)
 when @Split = 2 then DATEADD(MONTH,  @Split * 3, @date)
 when @Split = 3 then DATEADD(MONTH,  @Split , @date)
end
AS return_date

It would've been much easier if you'd stated the rules for adding right at the start.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
DROP FUNCTION dbo.your_func_name_here
GO
CREATE FUNCTION dbo.your_func_name_here 
(
    @StartDateTime date,
    @Split smallint
)
RETURNS date
AS
BEGIN
RETURN (
    SELECT DATEADD(MONTH, CASE @Split WHEN 1 THEN 12 WHEN 2 THEN 6 
        WHEN 3 THEN 3 ELSE NULL END, @StartDateTime) AS return_date
)
END
GO

--run a test
SELECT test_date, split, dbo.your_func_name_here(test_date, split) AS return_date
FROM ( VALUES('20190101', 1),('20190101', 2),('20190101',3) ) AS test_dates(test_date, split)

1 Like

That's perfect. Thank you Scott.

Luis