Time Generating Function within Time Intervals an

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

Transact-SQL (2008)

Time Generating Function within Time Intervals an

Author

Topic

rasta
Starting Member

23 Posts

Posted - 2011-12-06 : 12:22:31

I would like to generate a SQL function to generate time intervals where the output should be a table with two columns. 1st as a result of the following script where @startDate = '1/1/2007' and the 2nd one with @startDate = '1/31/2007'. Thanks!

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
DECLARE @TimePeriod NVARCHAR(50)
DECLARE @RptDate DATETIME
SET @startDate = '1/1/2007'
SET @endDate = '10/1/2010'
SET @TimePeriod = 'Quarterly'
SET @RptDate = @startDate
WHILE @RptDate < @endDate
BEGIN
PRINT @RptDate
IF @TimePeriod = 'Monthly'
SET @RptDate = DATEADD(mm, 1, DATEADD(dd, -DAY(@RptDate) + 1,@RptDate))
IF @TimePeriod = 'Weekly'
SET @RptDate = DATEADD(ww, 1, DATEADD(dd, -DATEPART(weekday,@RptDate) + 1,@RptDate))
IF @TimePeriod = 'Quarterly'
SET @RptDate = DATEADD(qq, 1, DATEADD(qq, DATEDIFF(qq, 0, @RptDate), -1) + 1)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 12:25:58

so what should be your output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rasta
Starting Member

23 Posts

Posted - 2011-12-06 : 12:38:39

For example, the outcome should looks like the following (however there are a few mistekas, e.g. insted of 30.12.xxxx in the outcome column should be 31.12.xxxx, the same for 30.03.xxxx)

;with cte as
(
select dte = CONVERT(datetime,'20070101'), dte2 = CONVERT(datetime,'20071231')
union all
select dte = DATEADD(mm,3,dte), dte2 = DATEADD(mm,3,dte2) from cte where dte < '20101001'
)
select cte.dte as quarter,cte.dte2 as outcome from cte

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 12:49:24

[code]
;with cte as
(
select dte = @startdate, dte2 = dateadd(mm,1,@startdate)-1
union all
select dte = DATEADD(qq,1,dte), dte2 = DATEADD(mm,1,DATEADD(qq,1,dte))
from cte
where DATEADD(mm,1,DATEADD(qq,1,dte)) < @enddate
)
select cte.dte as quarter,cte.dte2 as outcome from cte
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rasta
Starting Member

23 Posts

Posted - 2011-12-07 : 02:58:33

Thanks!
And what about user defined SQL function? I am tryiing to do the following but it does not work:

CREATE FUNCTION TimeGenerator2 (@startDate DATETIME, @endDate DATETIME,@TimePeriod NVARCHAR(50))
RETURNS @TimeGeneratorTable TABLE
(
[dte] datetime,
[dte] datetime
)
AS
BEGIN
DECLARE @RptDate DATETIME
SET @RptDate = @startDate
WHILE @RptDate < @endDate
BEGIN
--PRINT @RptDate --- This prints out the dates
IF @TimePeriod = 'Monthly'
SET @RptDate = DATEADD(mm, 1, DATEADD(dd, -DAY(@RptDate) + 1,@RptDate))
IF @TimePeriod = 'Weekly'
SET @RptDate = DATEADD(ww, 1, DATEADD(dd, -DATEPART(weekday,@RptDate) + 1,@RptDate))
IF @TimePeriod = 'Quarterly'
SET @RptDate = DATEADD(qq, 1, DATEADD(qq, DATEDIFF(qq, 0, @RptDate), -1) + 1)
END
UPDATE @TimeGeneratorTable
SET dte = @RptDate, dte2=DATEADD(dd,-1,DATEADD(yy,1,@RptDate))
RETURN
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 03:10:41

it should be like this i guess


CREATE FUNCTION TimeGenerator2 (@startDate DATETIME, @endDate DATETIME,@TimePeriod NVARCHAR(50))
RETURNS @TimeGeneratorTable TABLE
(
[dte1] datetime,
[dte2] datetime
)
AS
BEGIN
DECLARE @RptDate DATETIME 
SET @RptDate = @startDate 
WHILE @RptDate < @endDate 
BEGIN 
--PRINT @RptDate --- This prints out the dates 
IF @TimePeriod = 'Monthly' 
SET @RptDate = DATEADD(mm, 1, DATEADD(dd, -DAY(@RptDate) + 1,@RptDate)) 
IF @TimePeriod = 'Weekly' 
SET @RptDate = DATEADD(ww, 1, DATEADD(dd, -DATEPART(weekday,@RptDate) + 1,@RptDate)) 
IF @TimePeriod = 'Quarterly' 
SET @RptDate = DATEADD(qq, 1, DATEADD(qq, DATEDIFF(qq, 0, @RptDate), -1) + 1) 
INSERT @TimeGeneratorTable 
VALUES(@RptDate,DATEADD(dd,-1,DATEADD(yy,1,@RptDate)))
END 
RETURN
END
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rasta
Starting Member

23 Posts

Posted - 2011-12-07 : 04:46:23

Then, when I am trying

TimeGenerator2 (@startDate = '20070101', @endDate = '20101001' ,@TimePeriod = 'Quarterly')

An Error Occurs: "Incorrect syntax near '@startDate'."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 04:49:50

you cant call table valued function like this.you should call it as below

SELECT * FROM dbo.TimeGenerator2 ('20070101','20101001' ,'Quarterly')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources