SeriesDate
Updated: 17 August 2010
Use wct.SeriesDate to generate a range of date values.
Syntax
SELECT * FROM [Example].[wct].[SeriesDate] (
<@StartDate, datetime,>
,<@StopDate, datetime,>
,<@StepDays, float,>
,<@MaxIterations, float,>
,<@SeriesType, nvarchar(4000),>)
Arguments
@StartDate
the first date in the date range. The first value may be the maximum or the minimum value, depending on the @StepDays. @StartDate must be of the type datetime or of a type that implicitly converts to datetime.
@StopDate
the last value in the date range. The last value may be the maximum or the minimum value, depending on the @StepDays. @StopDate must be of the type datetime or of a type that implicitly converts to datetime.
@StepDays
the increment (or decrement) used to determine the next value in a linear series (see @SeriesType). In a random series, this is the multiple used to calculate a random date between the @StartDate and the @StopDate. @StepDays must be of the type float or of a type that implicitly converts to float.
@MaxIterations
the number of results to return. For linear series (see @SeriesType), a value of NULL will return all the values between the @StartDate and the @StopDate specified by the @StepDays. For random series, NULL is not permitted. @MaxIterations must be of the type float or of a type that implicitly converts to float. Non-integer values are truncated to integer.
@SeriesType
the letter ‘L’ or ‘l’ for linear series, or the letter ‘R’ or ‘r’ for random series. A linear series will generate numbers from the @StartDate incremented by the @StepDays until either the number of iterations is equal to @MaxIterations or the greatest value less than or equal to the @StopDate has been reached.
A random series will randomly generate number that are multiples of @StepDays until @MaxIterations is reached.
Return Types
RETURNS TABLE (
[Seq] [int] NULL,
[SeriesValue] [datetime] NULL
)
Remarks
· To generate a series for integer values use the SeriesInt function
· To generate a series for floating point numbers use the SeriesFloat function
· If the @StepDate > @StopDate and @SeriesType = ‘L’, then only one row will be returned with SeriesValue equal to @StartDate.
· If the @StepDate > @StopDate and @SeriesType = ‘R’, then @MaxIterations rows will be returned with SeriesValue equal to the lesser of @StartDate and @StopDate.
· If @SeriesType is NULL, then @SeriesType is set to ‘L’.
· If @StopDate is NULL, it will be calculated from @StartDate, @MaxIterations and @StepDays.
· If @StepDays is NULL and @SeriesType is ‘L’ and @StopDate > @StartDate then @StepDays is set to 1.
· If @StepDays is NULL and @SeriesType is ‘L’ and @StopDate < @StartDate then @StepDays is set to -1.
· If @SeriesType is ‘L’ and SIGN(@StepDays) <> SIGN(@StopDate - @StartDate) then only one row will be returned.
· If @SeriesType is ‘R’, @MaxIterations must be greater than 0 and not equal to NULL.
· If @SeriesType is ‘R’, @StartDate cannot be NULL
· If @SeriesType is ‘R’, @StopDate cannot be NULL.
Examples
Generate a list of dates from 5/15/2010 to 6/14/2010.
SELECT * FROM wct.SeriesDate ('05/15/2010','06/15/2010',1,NULL,'L')
Here is the resultant table:
Seq SeriesValue
----------- -----------------------
1 2010-05-15 00:00:00.000
2 2010-05-16 00:00:00.000
3 2010-05-17 00:00:00.000
4 2010-05-18 00:00:00.000
5 2010-05-19 00:00:00.000
6 2010-05-20 00:00:00.000
7 2010-05-21 00:00:00.000
8 2010-05-22 00:00:00.000
9 2010-05-23 00:00:00.000
10 2010-05-24 00:00:00.000
11 2010-05-25 00:00:00.000
12 2010-05-26 00:00:00.000
13 2010-05-27 00:00:00.000
14 2010-05-28 00:00:00.000
15 2010-05-29 00:00:00.000
16 2010-05-30 00:00:00.000
17 2010-05-31 00:00:00.000
18 2010-06-01 00:00:00.000
19 2010-06-02 00:00:00.000
20 2010-06-03 00:00:00.000
21 2010-06-04 00:00:00.000
22 2010-06-05 00:00:00.000
23 2010-06-06 00:00:00.000
24 2010-06-07 00:00:00.000
25 2010-06-08 00:00:00.000
26 2010-06-09 00:00:00.000
27 2010-06-10 00:00:00.000
28 2010-06-11 00:00:00.000
29 2010-06-12 00:00:00.000
30 2010-06-13 00:00:00.000
31 2010-06-14 00:00:00.000
32 2010-06-15 00:00:00.000
(32 row(s) affected)
Generate a list of times in 15 minute increments for 5/15/2010.
SELECT * FROM wct.SeriesDate ('05/15/2010','05/16/2010',1.0000/96.0000,NULL,'L')
Here’s the first 15 rows of the resultant table.
Seq SeriesValue
----------- -----------------------
1 2010-05-15 00:00:00.000
2 2010-05-15 00:15:00.000
3 2010-05-15 00:30:00.000
4 2010-05-15 00:45:00.000
5 2010-05-15 01:00:00.000
6 2010-05-15 01:15:00.000
7 2010-05-15 01:30:00.000
8 2010-05-15 01:45:00.000
9 2010-05-15 02:00:00.000
10 2010-05-15 02:15:00.000
11 2010-05-15 02:30:00.000
12 2010-05-15 02:45:00.000
13 2010-05-15 03:00:00.000
14 2010-05-15 03:15:00.000
15 2010-05-15 03:30:00.000
Let’s say we had a table with all the dates that the S&P 500 index was traded in 2009 and we wanted to find all the days that it was not traded on that were not a Saturday or a Sunday. We could enter something like this.
SELECT seriesvalue
FROM wct.SeriesDate('1/1/2009','12/31/2009',1,NULL,'L')
WHERE datepart(dw, seriesvalue) <> 7 and datepart(dw, seriesvalue) <> 1
EXCEPT
SELECT Date
FROM SANDP
This returns the following result.
seriesvalue
-----------------------
2009-01-01 00:00:00.000
2009-01-19 00:00:00.000
2009-02-16 00:00:00.000
2009-04-10 00:00:00.000
2009-05-25 00:00:00.000
2009-07-03 00:00:00.000
2009-09-07 00:00:00.000
2009-11-26 00:00:00.000
2009-12-25 00:00:00.000
(9 row(s) affected)
To randomly generate 20 dates between 1/1/2008 and and 12/31/2011, we would enter the following statement.
SELECT * FROM wct.SeriesDate('1/1/2008','12/31/2011',1,20,'R')
This returns the following result.
Seq SeriesValue
----------- -----------------------
1 2009-07-01 00:00:00.000
2 2008-12-14 00:00:00.000
3 2011-07-07 00:00:00.000
4 2011-12-25 00:00:00.000
5 2009-09-02 00:00:00.000
6 2009-01-11 00:00:00.000
7 2010-08-18 00:00:00.000
8 2011-04-02 00:00:00.000
9 2009-11-23 00:00:00.000
10 2010-05-16 00:00:00.000
11 2009-08-05 00:00:00.000
12 2010-07-04 00:00:00.000
13 2008-04-01 00:00:00.000
14 2010-04-25 00:00:00.000
15 2011-11-16 00:00:00.000
16 2010-09-03 00:00:00.000
17 2009-06-03 00:00:00.000
18 2008-07-27 00:00:00.000
19 2011-07-04 00:00:00.000
20 2011-04-19 00:00:00.000
(20 row(s) affected)
If we wanted the same query to only return dates that were not weekend dates, we could enter the following statement.
SELECT TOP 20 seq, seriesvalue FROM wct.SeriesDate('1/1/2008','12/31/2011',1,50,'R')
WHERE datepart(dw, seriesvalue) <> 7 and datepart(dw, seriesvalue) <> 1
This produces the following result.
seq seriesvalue
----------- -----------------------
1 2009-12-17 00:00:00.000
2 2008-09-19 00:00:00.000
4 2010-04-22 00:00:00.000
5 2008-07-17 00:00:00.000
6 2011-11-02 00:00:00.000
8 2009-12-09 00:00:00.000
9 2011-06-30 00:00:00.000
10 2008-10-14 00:00:00.000
12 2010-06-15 00:00:00.000
14 2011-01-31 00:00:00.000
16 2009-02-13 00:00:00.000
18 2008-04-15 00:00:00.000
19 2008-01-03 00:00:00.000
20 2011-05-10 00:00:00.000
21 2008-03-25 00:00:00.000
23 2008-09-22 00:00:00.000
25 2010-01-12 00:00:00.000
26 2008-05-02 00:00:00.000
30 2011-10-21 00:00:00.000
31 2010-05-28 00:00:00.000
(20 row(s) affected)