SQL - how to return Months from Date Range

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 2000 Forums

SQL Server Development (2000)

SQL - how to return Months from Date Range

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2006-08-16 : 20:57:59

Hi there,

I have a daterange which I need to return all the months within that date range (i.e. Jan, Feb, March). Does anyone have some sql script to achieve this?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-16 : 21:19:21

Something like this perhaps (you can wrap this into a function if you like)?

DECLARE @start_date datetime, @end_date datetime, @increment int
DECLARE @months TABLE (monthname varchar(10) NULL)

SET NOCOUNT ON

SET @start_date = '2006-01-10'
SET @end_date = '2006-05-27'

SET @increment = 0

WHILE DATEPART(month, DATEADD(month, @increment, @start_date)) <= DATEPART(month, @end_date)
BEGIN
INSERT INTO @months SELECT DATENAME(month, DATEADD(month, @increment, @start_date))
SET @increment = @increment +1
END

SELECT * FROM @months

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-16 : 22:39:22

You can do this with a date table.

Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


select
	YEAR_MONTH = max(YEAR_MONTH),
	MONTH = max(MONTH),
	YEAR_MONTH_NAME = max(YEAR_MONTH_NAME),
	YEAR_MONTH_NAME_LONG = max(YEAR_MONTH_NAME_LONG),
	MONTH_NAME = max(MONTH_NAME),
	MONTH_NAME_LONG = max(MONTH_NAME_LONG)
frOm
	-- Function from Script Library
	F_TABLE_DATE('20060101', '20071231')
group by
	MONTH_SEQ_NO
order by
	MONTH_SEQ_NO

Results:


YEAR_MONTH  MONTH YEAR_MONTH_NAME YEAR_MONTH_NAME_LONG MONTH_NAME MONTH_NAME_LONG 
----------- ----- --------------- -------------------- ---------- --------------- 
200601      1     2006 Jan        2006 January         Jan        January
200602      2     2006 Feb        2006 February        Feb        February
200603      3     2006 Mar        2006 March           Mar        March
200604      4     2006 Apr        2006 April           Apr        April
200605      5     2006 May        2006 May             May        May
200606      6     2006 Jun        2006 June            Jun        June
200607      7     2006 Jul        2006 July            Jul        July
200608      8     2006 Aug        2006 August          Aug        August
200609      9     2006 Sep        2006 September       Sep        September
200610      10    2006 Oct        2006 October         Oct        October
200611      11    2006 Nov        2006 November        Nov        November
200612      12    2006 Dec        2006 December        Dec        December
200701      1     2007 Jan        2007 January         Jan        January
200702      2     2007 Feb        2007 February        Feb        February
200703      3     2007 Mar        2007 March           Mar        March
200704      4     2007 Apr        2007 April           Apr        April
200705      5     2007 May        2007 May             May        May
200706      6     2007 Jun        2007 June            Jun        June
200707      7     2007 Jul        2007 July            Jul        July
200708      8     2007 Aug        2007 August          Aug        August
200709      9     2007 Sep        2007 September       Sep        September
200710      10    2007 Oct        2007 October         Oct        October
200711      11    2007 Nov        2007 November        Nov        November
200712      12    2007 Dec        2007 December        Dec        December

(24 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources