Function to convert a 'date range' to table of starting and ending

I have a table that contains two fields: effectiveFrom, effectiveTo.
The time elapsed between these day could be greater than one year.
I would like to develop a UDF that would convert these dates into a
table of values. For example, if the dates are 7/1/03 and 4/1/06 the
resulting table would look like:
startingDate endingDate
07/01/03 12/31/03
01/01/04 12/31/04
01/01/05 12/31/05
01/01/06 04/01/06
I am confident that I can do this in a stored procedure, but I'm having
difficulty in a table-set UDF.
Suggestions appreciated.
Craig BuchananAdi-
Not exactly what I was looking for, but useful in other situations.
Thanks a lot for your response.
Craig
Adi wrote:
> You can do it this way:
> create function ShowDates (@.StartDate smalldatetime, @.EndDate
> smalldatetime)
> returns @.DatesTable table (DateCol smalldatetime)
> as
> begin
> while @.startDate <= @.EndDate
> BEGIN
> insert into @.DatesTable (DateCol) values (@.StartDate)
> set @.StartDate = dateadd(dd,1, @.StartDate)
> END
> return
> END
> If you are using SQL Server 2005, then you can use recursive CTE to do
> the same thing
>|||Razvan-
Perfect! Thanks a lot.
Craig Buchanan
Razvan Socol wrote:
> Hello, Craig
> You can use this function (after you give it a better name):
> CREATE FUNCTION YourFunctionName(
> @.EffectiveFrom datetime,
> @.EffectiveTo datetime
> )
> RETURNS @.Result TABLE (
> StartingDate datetime NOT NULL PRIMARY KEY,
> EndingDate datetime NOT NULL UNIQUE,
> CHECK (startingDate<=endingDate)
> ) AS BEGIN
> WHILE YEAR(@.EffectiveFrom)<YEAR(@.EffectiveTo) BEGIN
> DECLARE @.EndOfYear datetime
> SET @.EndOfYear=DATEADD(year,DATEDIFF(year,0,
@.EffectiveFrom)+1,0)-1
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EndOfYear)
> SET @.EffectiveFrom=@.EndOfYear+1
> END
> IF @.EffectiveFrom<=@.EffectiveTo BEGIN
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EffectiveTo)
> END
> RETURN
> END
> Razvan
>|||Uri-
Thanks for the reply. Not exactly what I needed, but I can use this in
another situation.
Thanks,
Craig
Uri Dimant wrote:
> Try this one
> CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> DECLARE @.rc AS INT
> SET @.rc = 1
> INSERT INTO @.Dates VALUES(@.from)
> WHILE @.from + @.rc * 2 - 1 <= @.to
> BEGIN
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> SET @.rc = @.rc * 2
> END
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> WHERE dt + @.rc <= @.to
> RETURN
> END
> GO
> SELECT dt FROM fn_dates('20060101', '20060130')
>
> "Craig Buchanan" <user@.example.net> wrote in message
> news:OAAuwYKMGHA.2992@.tk2msftngp13.phx.gbl...
>