March 11, 2010 at 12:14 pm
Hi i m having a requirement where i need to include all the months between two date for ex:
select DATEDIFF(mm,'10/1/2009','1/1/2010') gives us 3
because its taking the diff of 2 dates i want to include all the months in the date tat means in the above example i need to display
Oct, Nov,Dec,Jan ........
and i need to display datediff =4
Thanks
March 11, 2010 at 10:05 pm
SELECTCOUNT(*)
FROM(
SELECT DATENAME( MONTH, DATEADD( DAY, N - 1, @sdtStartDate ) ) Col1, COUNT(*) Col2
FROM dbo.Tally
WHERE N <= @iDays
GROUP BY DATENAME( MONTH, DATEADD( DAY, N - 1, @sdtStartDate ) )
) T
Here "dbo.Tally" is a Tally Table. Check the following link to create a Tally Table
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 12, 2010 at 12:05 am
dheer (3/11/2010)
Hi i m having a requirement where i need to include all the months between two date for ex:select DATEDIFF(mm,'10/1/2009','1/1/2010') gives us 3
because its taking the diff of 2 dates i want to include all the months in the date tat means in the above example i need to display
Oct, Nov,Dec,Jan ........
and i need to display datediff =4
Thanks
For clarification, you want to display the month names that would be included in the month difference comparison?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2010 at 5:10 am
Hi dheer,
You can try the following select query and if it works for you, you can include it in a simple function
SELECT 1 + ( DATEDIFF(mm, 0, @d2) - DATEDIFF(mm, 0, @d1) )
I hope that helps,
Eralper
March 12, 2010 at 9:28 am
I solved a similar problem using a table-valued function that took two dates, used a while loop that iterates through the dates using DATEADD(...), and loaded each new date into the return table. I've modified my code to meet your needs here:
/*
-- =============================================
-- Author: Michael Baria
-- Create date: 3/12/2010
-- Description: Returns a table of months for a given date range.
-- =============================================
*/
CREATE FUNCTION [dbo].[GetMonths](@StartDate DATETIME, @EndDate DATETIME)
RETURNS @MonthList TABLE(MonthValue VARCHAR(15) NOT NULL)
AS
BEGIN
--Variable used to hold each new date value
DECLARE @DateValue DATETIME
--Start with the starting date in the range
SET @DateValue=@StartDate
--Load output table with the month part of each new date
WHILE @DateValue <= @EndDate
BEGIN
INSERT INTO @MonthList(MonthValue)
SELECT DATENAME(mm,@DateValue)
--Move to the next month
SET @DateValue=DATEADD(mm,1,@DateValue)
END
--Return results
RETURN
END
You would use it like this:
SELECT * FROM [dbo].[GetMonths] ('1/1/2010','12/1/2010')
March 12, 2010 at 11:42 am
Uncle Moki (3/12/2010)
I solved a similar problem using a table-valued function that took two dates, used a while loop that iterates through the dates using DATEADD(...), and loaded each new date into the return table. I've modified my code to meet your needs here:
/*
-- =============================================
-- Author: Michael Baria
-- Create date: 3/12/2010
-- Description: Returns a table of months for a given date range.
-- =============================================
*/
CREATE FUNCTION [dbo].[GetMonths](@StartDate DATETIME, @EndDate DATETIME)
RETURNS @MonthList TABLE(MonthValue VARCHAR(15) NOT NULL)
AS
BEGIN
--Variable used to hold each new date value
DECLARE @DateValue DATETIME
--Start with the starting date in the range
SET @DateValue=@StartDate
--Load output table with the month part of each new date
WHILE @DateValue <= @EndDate
BEGIN
INSERT INTO @MonthList(MonthValue)
SELECT DATENAME(mm,@DateValue)
--Move to the next month
SET @DateValue=DATEADD(mm,1,@DateValue)
END
--Return results
RETURN
END
You would use it like this:
SELECT * FROM [dbo].[GetMonths] ('1/1/2010','12/1/2010')
Nice solution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2010 at 12:52 pm
I'd rather use an inline table-valued function (ITVF)...
CREATE FUNCTION [dbo].[GetMonths](@StartDate DATETIME, @EndDate DATETIME)
RETURNS TABLE
AS
RETURN
(
SELECT DATEADD(mm,number,@StartDate) AS MonthValue
FROM master.dbo.spt_values
WHERE TYPE ='P'
AND number<=DATEDIFF(mm,@StartDate,@EndDate)
);
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply