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?
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)
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
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
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.
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)