SQL Server SeriesDate Function

Updated 2023-10-18 19:47:05.467000

Description

Use the table-valued function SeriesDate to generate a series of date values.

Syntax

SELECT * FROM [westclintech].[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 Type

table

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:

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 resultant table.

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.

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.

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.