Sam Teng Wong Sam Teng Wong - 6 months ago 13
SQL Question

Get Year of parameter dates

Is there a way for me to get the year of the months based on the parameter given?

DECLARE @StartDate DATETIME,
@EndDate DATETIME;

SELECT @StartDate = '2016-02-01'
,@EndDate = '2017-03-01';



SELECT
/*is there a way for me to get the year of months based on the parameter dates*/
/*DATEPART(YEAR, DATEADD(YEAR, x.number, @StartDate)) AS Year,*/
DATEPART(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS Month,
DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)


enter image description here

the result of my query....

what I want is that from february to december it'll have a year of 2016 and from january to march it'll have a 2017 year..... based on the parameter that i've given..

thanks in advance.

Answer

Try this:

SELECT YEAR(y.dt) AS Year,
       MONTH(y.dt) AS Month,
       DATENAME(MONTH, y.dt) AS MonthName
FROM    master.dbo.spt_values x
CROSS APPLY (SELECT DATEADD(MONTH, x.number, @StartDate)) AS y(dt)
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)

Demo here

Comments