SQL Date Help

[cotton31]

Hi,

Is there a way in T-SQL to return all of the months between two given dates?

For example, if the two dates were 01/01/2010 and 01/03/2010, it would return Jan, Feb, Mar.

Can this be done? I'm using SQL Server 2005 if that makes any difference?

Thanks,

 

[djkav]

I'm guessing that you'd have to loop thru the months using the 2 dates.

You could bundle it all into a function to make it tidy

 

[Pho]

Unless there's a built-in function to do this something like this should work:

DECLARE @StartDate DateTime = '2010/01/01'
DECLARE @EndDate DateTime = '2010/03/01'
DECLARE @CurrentDate DateTime = @StartDate

DECLARE @results TABLE (Month DateTime PRIMARY KEY)

WHILE (@CurrentDate <= @EndDate)
BEGIN
	INSERT @results (Month) Values (@CurrentDate)
	SET @CurrentDate = DATEADD(M, 1, @CurrentDate)
END

SELECT * FROM @results

and as djkav says, you could easily wrap that into a function so you can reuse it.

 

[cotton31]

Thanks Pho - that worked great!

Next challenge is;

I have another table containing a reference and the two dates used above.

How would I adapt the above, so that the output would be:

Ref , Month
REF001, Jan
REF001, Feb
REF001, Mar
REF002, May
REF002, Jun
REF003, Mar
REF003, Apr
REF003, May
etc.

I'm assuming I need another WHILE loop around the above, to loop through each reference and doing the date calculation before moving on to the next reference. How would I tell it to move on to the next reference? (The above example is for illustration purposes only - each ref is move complex than that and not necessarily sequential.)

Can you help?

Thanks,

 

[djkav]

Sounds like you could use the table that contains the REF field. Select all references and MONTH_NAME for DATE that fall between DATE1 and DATE2.

 

[Pho]

I'd first convert that first bit of SQL into a function. This might not be the most efficient way of doing this, but it should work.

Copy/paste this into management studio (or whatever you use) and execute it. It will create a multi-statement table-valued function called GetDateRange:

/****** Object:  UserDefinedFunction [dbo].[GetDateRange]    Script Date: 12/03/2010 14:51:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetDateRange]
(
	@StartDate	DateTime,
	@EndDate	DateTime
)
RETURNS 
@results TABLE 
(
	Month DateTime Primary Key
)
AS
BEGIN
	DECLARE @CurrentDate DateTime = @StartDate

	WHILE (@CurrentDate <= @EndDate)
	BEGIN
		INSERT @results (Month) Values (@CurrentDate)
		SET @CurrentDate = DATEADD(M, 1, @CurrentDate)
	END
	
	return;
END

GO

You can then query GetDateRange with the following:

SELECT * FROM [dbo].[GetDateRange] ('2010/01/01', '2100/01/01')

As it's now a function you can easily use it with your queries. So back to your original problem you can do something like this:

SELECT Reference.ReferenceNo, DateRange.Month FROM Reference
	CROSS APPLY dbo.GetDateRange(Reference.StartDate, Reference.EndDate) DateRange

For each row in reference it 'applys' the GetDateRange function by passing to it the start and end date from the reference table and merges them together.


Given sample data of:

ReferenceNo	StartDate	EndDate
REF001	2010-01-01 00:00:00.000	2010-03-01 00:00:00.000
REF002	2010-02-03 00:00:00.000	2010-09-03 00:00:00.000
REF0027	2009-06-01 00:00:00.000	2011-06-01 00:00:00.000

I get the following output:

ReferenceNo	Month
REF001	2010-01-01 00:00:00.000
REF001	2010-02-01 00:00:00.000
REF001	2010-03-01 00:00:00.000
REF002	2010-02-03 00:00:00.000
REF002	2010-03-03 00:00:00.000
REF002	2010-04-03 00:00:00.000
REF002	2010-05-03 00:00:00.000
REF002	2010-06-03 00:00:00.000
REF002	2010-07-03 00:00:00.000
REF002	2010-08-03 00:00:00.000
REF002	2010-09-03 00:00:00.000
REF0027	2009-06-01 00:00:00.000
REF0027	2009-07-01 00:00:00.000
REF0027	2009-08-01 00:00:00.000
REF0027	2009-09-01 00:00:00.000
REF0027	2009-10-01 00:00:00.000
REF0027	2009-11-01 00:00:00.000
REF0027	2009-12-01 00:00:00.000
REF0027	2010-01-01 00:00:00.000
REF0027	2010-02-01 00:00:00.000
REF0027	2010-03-01 00:00:00.000
REF0027	2010-04-01 00:00:00.000
REF0027	2010-05-01 00:00:00.000
REF0027	2010-06-01 00:00:00.000
REF0027	2010-07-01 00:00:00.000
REF0027	2010-08-01 00:00:00.000
REF0027	2010-09-01 00:00:00.000
REF0027	2010-10-01 00:00:00.000
REF0027	2010-11-01 00:00:00.000
REF0027	2010-12-01 00:00:00.000
REF0027	2011-01-01 00:00:00.000
REF0027	2011-02-01 00:00:00.000
REF0027	2011-03-01 00:00:00.000
REF0027	2011-04-01 00:00:00.000
REF0027	2011-05-01 00:00:00.000
REF0027	2011-06-01 00:00:00.000

(note that APPLY will only work for SQL Server 2005+, see Microsoft)

 

[cotton31]

Pho that looks like just what I'm after - I'll give it a go tomorrow when I'm back in the office. Never heard of CROSS APPLY before.