Print Month between 2 date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Print Month between 2 date

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2009-06-24 : 14:54:48

Hello all,
I am writing this query below
DECLARE @Date1 datetime, @Date2 datetime,@icount int
declare @month int,@Max int

SET @Date1 = '2009-03-25 00:00:00.000'
SET @Date2 ='2009-08-07 00:00:00.000'
SET @Max = (MONTH(@date2) - MONTH(@date1) + 1 )
SET @icount = 1
SET @month = MONTH(@DATE1)

WHILE @icount <= @Max
BEGIN
PRINT year(@Date2) * 100 + @month
SET @month = @month + 1
SET @icount = @icount + 1
END

it work for me if @date1 and @date2 are in the SAME YEAR. can some one help me to get the result if they are in different year?

if @date1 ='11/03/2008' and @date2 = '02/01/2009'
the result should be:
200811
200812
200901
200902.

Appreciate your help. Thanks.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-24 : 15:16:23

This should work, although there may be an easier solution:

DECLARE @Date1 datetime, @Date2 datetime, @icount int, @month int, @Max int

SET @Date1 = '11/03/2008'
SET @Date2 = '02/01/2009'
SET @Max = DATEDIFF(mm, @Date1, @Date2)+1
SET @icount = 1
SET @month = MONTH(@Date1)

WHILE @icount <= @Max
BEGIN
	PRINT YEAR(DATEADD(mm, @icount-1, @Date1))*100 + MONTH(DATEADD(mm, @icount-1, @Date1))
	SET @month = @month + 1
	SET @icount = @icount + 1
END

Some days you're the dog, and some days you're the fire hydrant.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-24 : 15:37:41

Every day I learn something that Peso already knew.

DECLARE @Date1 datetime, @Date2 datetime
SET @Date1 = '11/03/2008'
SET @Date2 ='09/01/2009'

SELECT YEAR(DATEADD(month,v.Number,@Date1))*100 +month(DATEADD(month,v.Number,@Date1))
FROM
master..spt_values AS v
WHERE v.Type = 'P'
AND v.Number >= 1
AND DATEADD(month,v.Number,@Date1) < @Date2

Jim

Every day I learn something that Peso already knew.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 16:27:01

[code]DECLARE @Date1 DATETIME,
@Date2 DATETIME

SELECT @Date1 = '20081103',
@Date2 = '20090201'

-- Peso 1
;WITH Yak(theYearMonth, theDate)
AS (
SELECT CONVERT(CHAR(6), @Date1, 112),
@Date1

UNION ALL

SELECT CONVERT(CHAR(6), DATEADD(MONTH, 1, theDate), 112),
DATEADD(MONTH, 1, theDate)
FROM Yak
WHERE DATEDIFF(MONTH, theDate, @Date2) > 0
)
SELECT theYearMonth
FROM Yak
ORDER BY theYearMonth

-- Peso 2
SELECT theYearMonth
FROM (
SELECT CONVERT(CHAR(6), DATEADD(MONTH, Number, @Date1), 112) AS theYearMonth
FROM master..spt_values
WHERE Type = 'P'
AND Number <= DATEDIFF(MONTH, @Date1, @Date2)
) AS d
WHERE theYearMonth BETWEEN CONVERT(CHAR(6), @Date1, 112) AND CONVERT(CHAR(6), @Date2, 112)
ORDER BY theYearMonth[/code]

E 12°55'05.63"
N 56°04'39.26"

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2009-06-24 : 16:30:58

Thank you all for your help, i've been trying to use your queries
in my procedure, however, i couldn't figure it out.
i have a table like this:
Create table #temp(Date1 datetime, Date2 datetime)
INSERT INTO #temp
SELECT '10/1/2008','2/1/2009'
UNION
SELECT '2/1/2009','4/1/2009'

What i'd like to have is a select statement
with a result like this:
Date1 Date2 TheYearMonth
10/1/2008 2/1/2009 200810
10/1/2008 2/1/2009 200811
10/1/2008 2/1/2009 200812
10/1/2008 2/1/2009 200901
10/1/2008 2/1/2009 200902
2/1/2009 4/1/2009 200902
2/1/2009 4/1/2009 200903
2/1/2009 4/1/2009 200904

Please help. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 18:36:55

[code]DECLARE @Sample TABLE
(
Date1 DATETIME,
Date2 DATETIME
)

INSERT @Sample
SELECT '10/1/2008', '2/1/2009' UNION ALL
SELECT '2/1/2009', '4/1/2009'

SELECT *
FROM @Sample AS s
CROSS APPLY (
SELECT CONVERT(CHAR(6), DATEADD(MONTH, Number, Date1), 112) AS theYearMonth
FROM master..spt_values
WHERE Type = 'P'
AND Number <= DATEDIFF(MONTH, Date1, Date2)
) AS d
WHERE theYearMonth BETWEEN CONVERT(CHAR(6), Date1, 112) AND CONVERT(CHAR(6), Date2, 112)
ORDER BY theYearMonth[/code]

E 12°55'05.63"
N 56°04'39.26"

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2009-06-24 : 18:47:28

Thanks so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources