Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server generate a range of dates


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)



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service