SeriesDate

Updated 2023-10-18 19:47:05.467000

Syntax

SELECT * FROM [westclintech].[wct].[SeriesDate] (
  <@StartDate, datetime,>
 ,<@StopDate, datetime,>
 ,<@StepDays, float,>
 ,<@MaxIterations, float,>
 ,<@SeriesType, nvarchar(4000),>)

Description

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

Arguments

@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.

@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.

@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.

@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.

@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.

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "424c9ab4-a937-4eff-97f1-adbed256ef6d", "colName": "Seq", "colDatatype": "int", "colDesc": "A monotonically increasing sequence number"}, {"id": "9a8c9090-0c7d-430e-8f84-52a365996867", "colName": "SeriesValue", "colDatatype": "datetime", "colDesc": "The date variable"}]}

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:

{"columns":[{"field":"Seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"SeriesValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Seq":"1","SeriesValue":"2010-05-15 00:00:00.000"},{"Seq":"2","SeriesValue":"2010-05-16 00:00:00.000"},{"Seq":"3","SeriesValue":"2010-05-17 00:00:00.000"},{"Seq":"4","SeriesValue":"2010-05-18 00:00:00.000"},{"Seq":"5","SeriesValue":"2010-05-19 00:00:00.000"},{"Seq":"6","SeriesValue":"2010-05-20 00:00:00.000"},{"Seq":"7","SeriesValue":"2010-05-21 00:00:00.000"},{"Seq":"8","SeriesValue":"2010-05-22 00:00:00.000"},{"Seq":"9","SeriesValue":"2010-05-23 00:00:00.000"},{"Seq":"10","SeriesValue":"2010-05-24 00:00:00.000"},{"Seq":"11","SeriesValue":"2010-05-25 00:00:00.000"},{"Seq":"12","SeriesValue":"2010-05-26 00:00:00.000"},{"Seq":"13","SeriesValue":"2010-05-27 00:00:00.000"},{"Seq":"14","SeriesValue":"2010-05-28 00:00:00.000"},{"Seq":"15","SeriesValue":"2010-05-29 00:00:00.000"},{"Seq":"16","SeriesValue":"2010-05-30 00:00:00.000"},{"Seq":"17","SeriesValue":"2010-05-31 00:00:00.000"},{"Seq":"18","SeriesValue":"2010-06-01 00:00:00.000"},{"Seq":"19","SeriesValue":"2010-06-02 00:00:00.000"},{"Seq":"20","SeriesValue":"2010-06-03 00:00:00.000"},{"Seq":"21","SeriesValue":"2010-06-04 00:00:00.000"},{"Seq":"22","SeriesValue":"2010-06-05 00:00:00.000"},{"Seq":"23","SeriesValue":"2010-06-06 00:00:00.000"},{"Seq":"24","SeriesValue":"2010-06-07 00:00:00.000"},{"Seq":"25","SeriesValue":"2010-06-08 00:00:00.000"},{"Seq":"26","SeriesValue":"2010-06-09 00:00:00.000"},{"Seq":"27","SeriesValue":"2010-06-10 00:00:00.000"},{"Seq":"28","SeriesValue":"2010-06-11 00:00:00.000"},{"Seq":"29","SeriesValue":"2010-06-12 00:00:00.000"},{"Seq":"30","SeriesValue":"2010-06-13 00:00:00.000"},{"Seq":"31","SeriesValue":"2010-06-14 00:00:00.000"},{"Seq":"32","SeriesValue":"2010-06-15 00:00:00.000"}]}

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.

{"columns":[{"field":"Seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"SeriesValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Seq":"1","SeriesValue":"2010-05-15 00:00:00.000"},{"Seq":"2","SeriesValue":"2010-05-15 00:15:00.000"},{"Seq":"3","SeriesValue":"2010-05-15 00:30:00.000"},{"Seq":"4","SeriesValue":"2010-05-15 00:45:00.000"},{"Seq":"5","SeriesValue":"2010-05-15 01:00:00.000"},{"Seq":"6","SeriesValue":"2010-05-15 01:15:00.000"},{"Seq":"7","SeriesValue":"2010-05-15 01:30:00.000"},{"Seq":"8","SeriesValue":"2010-05-15 01:45:00.000"},{"Seq":"9","SeriesValue":"2010-05-15 02:00:00.000"},{"Seq":"10","SeriesValue":"2010-05-15 02:15:00.000"},{"Seq":"11","SeriesValue":"2010-05-15 02:30:00.000"},{"Seq":"12","SeriesValue":"2010-05-15 02:45:00.000"},{"Seq":"13","SeriesValue":"2010-05-15 03:00:00.000"},{"Seq":"14","SeriesValue":"2010-05-15 03:15:00.000"},{"Seq":"15","SeriesValue":"2010-05-15 03:30:00.000"},{"Seq":"16","SeriesValue":"2010-05-15 03:45:00.000"},{"Seq":"17","SeriesValue":"2010-05-15 04:00:00.000"},{"Seq":"18","SeriesValue":"2010-05-15 04:15:00.000"},{"Seq":"19","SeriesValue":"2010-05-15 04:30:00.000"},{"Seq":"20","SeriesValue":"2010-05-15 04:45:00.000"},{"Seq":"21","SeriesValue":"2010-05-15 05:00:00.000"},{"Seq":"22","SeriesValue":"2010-05-15 05:15:00.000"},{"Seq":"23","SeriesValue":"2010-05-15 05:30:00.000"},{"Seq":"24","SeriesValue":"2010-05-15 05:45:00.000"},{"Seq":"25","SeriesValue":"2010-05-15 06:00:00.000"},{"Seq":"26","SeriesValue":"2010-05-15 06:15:00.000"},{"Seq":"27","SeriesValue":"2010-05-15 06:30:00.000"},{"Seq":"28","SeriesValue":"2010-05-15 06:45:00.000"},{"Seq":"29","SeriesValue":"2010-05-15 07:00:00.000"},{"Seq":"30","SeriesValue":"2010-05-15 07:15:00.000"},{"Seq":"31","SeriesValue":"2010-05-15 07:30:00.000"},{"Seq":"32","SeriesValue":"2010-05-15 07:45:00.000"},{"Seq":"33","SeriesValue":"2010-05-15 08:00:00.000"},{"Seq":"34","SeriesValue":"2010-05-15 08:15:00.000"},{"Seq":"35","SeriesValue":"2010-05-15 08:30:00.000"},{"Seq":"36","SeriesValue":"2010-05-15 08:45:00.000"},{"Seq":"37","SeriesValue":"2010-05-15 09:00:00.000"},{"Seq":"38","SeriesValue":"2010-05-15 09:15:00.000"},{"Seq":"39","SeriesValue":"2010-05-15 09:30:00.000"},{"Seq":"40","SeriesValue":"2010-05-15 09:45:00.000"},{"Seq":"41","SeriesValue":"2010-05-15 10:00:00.000"},{"Seq":"42","SeriesValue":"2010-05-15 10:15:00.000"},{"Seq":"43","SeriesValue":"2010-05-15 10:30:00.000"},{"Seq":"44","SeriesValue":"2010-05-15 10:45:00.000"},{"Seq":"45","SeriesValue":"2010-05-15 11:00:00.000"},{"Seq":"46","SeriesValue":"2010-05-15 11:15:00.000"},{"Seq":"47","SeriesValue":"2010-05-15 11:30:00.000"},{"Seq":"48","SeriesValue":"2010-05-15 11:45:00.000"},{"Seq":"49","SeriesValue":"2010-05-15 12:00:00.000"},{"Seq":"50","SeriesValue":"2010-05-15 12:15:00.000"},{"Seq":"51","SeriesValue":"2010-05-15 12:30:00.000"},{"Seq":"52","SeriesValue":"2010-05-15 12:45:00.000"},{"Seq":"53","SeriesValue":"2010-05-15 13:00:00.000"},{"Seq":"54","SeriesValue":"2010-05-15 13:15:00.000"},{"Seq":"55","SeriesValue":"2010-05-15 13:30:00.000"},{"Seq":"56","SeriesValue":"2010-05-15 13:45:00.000"},{"Seq":"57","SeriesValue":"2010-05-15 14:00:00.000"},{"Seq":"58","SeriesValue":"2010-05-15 14:15:00.000"},{"Seq":"59","SeriesValue":"2010-05-15 14:30:00.000"},{"Seq":"60","SeriesValue":"2010-05-15 14:45:00.000"},{"Seq":"61","SeriesValue":"2010-05-15 15:00:00.000"},{"Seq":"62","SeriesValue":"2010-05-15 15:15:00.000"},{"Seq":"63","SeriesValue":"2010-05-15 15:30:00.000"},{"Seq":"64","SeriesValue":"2010-05-15 15:45:00.000"},{"Seq":"65","SeriesValue":"2010-05-15 16:00:00.000"},{"Seq":"66","SeriesValue":"2010-05-15 16:15:00.000"},{"Seq":"67","SeriesValue":"2010-05-15 16:30:00.000"},{"Seq":"68","SeriesValue":"2010-05-15 16:45:00.000"},{"Seq":"69","SeriesValue":"2010-05-15 17:00:00.000"},{"Seq":"70","SeriesValue":"2010-05-15 17:15:00.000"},{"Seq":"71","SeriesValue":"2010-05-15 17:30:00.000"},{"Seq":"72","SeriesValue":"2010-05-15 17:45:00.000"},{"Seq":"73","SeriesValue":"2010-05-15 18:00:00.000"},{"Seq":"74","SeriesValue":"2010-05-15 18:15:00.000"},{"Seq":"75","SeriesValue":"2010-05-15 18:30:00.000"},{"Seq":"76","SeriesValue":"2010-05-15 18:45:00.000"},{"Seq":"77","SeriesValue":"2010-05-15 19:00:00.000"},{"Seq":"78","SeriesValue":"2010-05-15 19:15:00.000"},{"Seq":"79","SeriesValue":"2010-05-15 19:30:00.000"},{"Seq":"80","SeriesValue":"2010-05-15 19:45:00.000"},{"Seq":"81","SeriesValue":"2010-05-15 20:00:00.000"},{"Seq":"82","SeriesValue":"2010-05-15 20:15:00.000"},{"Seq":"83","SeriesValue":"2010-05-15 20:30:00.000"},{"Seq":"84","SeriesValue":"2010-05-15 20:45:00.000"},{"Seq":"85","SeriesValue":"2010-05-15 21:00:00.000"},{"Seq":"86","SeriesValue":"2010-05-15 21:15:00.000"},{"Seq":"87","SeriesValue":"2010-05-15 21:30:00.000"},{"Seq":"88","SeriesValue":"2010-05-15 21:45:00.000"},{"Seq":"89","SeriesValue":"2010-05-15 22:00:00.000"},{"Seq":"90","SeriesValue":"2010-05-15 22:15:00.000"},{"Seq":"91","SeriesValue":"2010-05-15 22:30:00.000"},{"Seq":"92","SeriesValue":"2010-05-15 22:45:00.000"},{"Seq":"93","SeriesValue":"2010-05-15 23:00:00.000"},{"Seq":"94","SeriesValue":"2010-05-15 23:15:00.000"},{"Seq":"95","SeriesValue":"2010-05-15 23:30:00.000"},{"Seq":"96","SeriesValue":"2010-05-15 23:45:00.000"},{"Seq":"97","SeriesValue":"2010-05-16 00:00: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.

{"columns":[{"field":"seriesvalue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"seriesvalue":"2009-01-01 00:00:00.000"},{"seriesvalue":"2009-01-19 00:00:00.000"},{"seriesvalue":"2009-02-16 00:00:00.000"},{"seriesvalue":"2009-04-10 00:00:00.000"},{"seriesvalue":"2009-05-25 00:00:00.000"},{"seriesvalue":"2009-07-03 00:00:00.000"},{"seriesvalue":"2009-09-07 00:00:00.000"},{"seriesvalue":"2009-11-26 00:00:00.000"},{"seriesvalue":"2009-12-25 00:00:00.000"}]}

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.

{"columns":[{"field":"Seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"SeriesValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Seq":"1","SeriesValue":"2009-07-01 00:00:00.000"},{"Seq":"2","SeriesValue":"2008-12-14 00:00:00.000"},{"Seq":"3","SeriesValue":"2011-07-07 00:00:00.000"},{"Seq":"4","SeriesValue":"2011-12-25 00:00:00.000"},{"Seq":"5","SeriesValue":"2009-09-02 00:00:00.000"},{"Seq":"6","SeriesValue":"2009-01-11 00:00:00.000"},{"Seq":"7","SeriesValue":"2010-08-18 00:00:00.000"},{"Seq":"8","SeriesValue":"2011-04-02 00:00:00.000"},{"Seq":"9","SeriesValue":"2009-11-23 00:00:00.000"},{"Seq":"10","SeriesValue":"2010-05-16 00:00:00.000"},{"Seq":"11","SeriesValue":"2009-08-05 00:00:00.000"},{"Seq":"12","SeriesValue":"2010-07-04 00:00:00.000"},{"Seq":"13","SeriesValue":"2008-04-01 00:00:00.000"},{"Seq":"14","SeriesValue":"2010-04-25 00:00:00.000"},{"Seq":"15","SeriesValue":"2011-11-16 00:00:00.000"},{"Seq":"16","SeriesValue":"2010-09-03 00:00:00.000"},{"Seq":"17","SeriesValue":"2009-06-03 00:00:00.000"},{"Seq":"18","SeriesValue":"2008-07-27 00:00:00.000"},{"Seq":"19","SeriesValue":"2011-07-04 00:00:00.000"},{"Seq":"20","SeriesValue":"2011-04-19 00:00:00.000"}]}

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.

{"columns":[{"field":"seq","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"seriesvalue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"seq":"1","seriesvalue":"2009-12-17 00:00:00.000"},{"seq":"2","seriesvalue":"2008-09-19 00:00:00.000"},{"seq":"4","seriesvalue":"2010-04-22 00:00:00.000"},{"seq":"5","seriesvalue":"2008-07-17 00:00:00.000"},{"seq":"6","seriesvalue":"2011-11-02 00:00:00.000"},{"seq":"8","seriesvalue":"2009-12-09 00:00:00.000"},{"seq":"9","seriesvalue":"2011-06-30 00:00:00.000"},{"seq":"10","seriesvalue":"2008-10-14 00:00:00.000"},{"seq":"12","seriesvalue":"2010-06-15 00:00:00.000"},{"seq":"14","seriesvalue":"2011-01-31 00:00:00.000"},{"seq":"16","seriesvalue":"2009-02-13 00:00:00.000"},{"seq":"18","seriesvalue":"2008-04-15 00:00:00.000"},{"seq":"19","seriesvalue":"2008-01-03 00:00:00.000"},{"seq":"20","seriesvalue":"2011-05-10 00:00:00.000"},{"seq":"21","seriesvalue":"2008-03-25 00:00:00.000"},{"seq":"23","seriesvalue":"2008-09-22 00:00:00.000"},{"seq":"25","seriesvalue":"2010-01-12 00:00:00.000"},{"seq":"26","seriesvalue":"2008-05-02 00:00:00.000"},{"seq":"30","seriesvalue":"2011-10-21 00:00:00.000"},{"seq":"31","seriesvalue":"2010-05-28 00:00:00.000"}]}