GENERATE_SERIES is a persnickety little function

New in SQL Server 2022 is the GENERATE_TIMESERIES table valued function. It's a handy function to generate a series of numbers. Once you understand why a number table is handy, you'll find lots of uses for it but this it not the blog post to cover it.

I wanted to generate a time series using the new hotness and step 1 is to get a list of the timeoffsets I'll add to my starting value. The following query will generate 16565 rows, which I'd then use as the second argument to a DATEADD call.

SELECT *, DATEADD(SECOND, Works.Value, DATETIME2FROMPARTS(2022,11,22,0,0,0,0,1)) FROM GENERATE_SERIES(0, 19565, 1) AS Works;

Perfect, now I have a row for each second from midnight to approximately 5.5 hours later. What if my duration need to vary because I'm going to compute these ranges for a number of different scenarios? I should make that 19565 into a variable and let's overengineer this by making it a bigint.

-- Assume the duration might be "big"
-- TODO: @duration = DATEDIFF(SECOND, Start, Stop)
DECLARE @duration bigint = 19565;
SELECT *, DATEADD(SECOND, LolWorks.Value, DATETIME2FROMPARTS(2022,11,22,0,0,0,0,1)) FROM GENERATE_SERIES(0, @duration, 1) AS LolWorks;

In the immortal words of Press Your Luck, WHAMMIE WHAMMIE WHAMMIE!

Msg 5373, Level 16, State 1, Line 4
All the input parameters should be of the same type. Supported types are tinyint, smallint, int, bigint, decimal and numeric.
Msg 206, Level 16, State 2, Line 4
Operand type clash: int is incompatible with void type
Msg 206, Level 16, State 2, Line 4
Operand type clash: bigint is incompatible with void type
Msg 206, Level 16, State 2, Line 4
Operand type clash: int is incompatible with void type

The fix is easy - just as the error message says, all THREE operands to GENERATE_SERIES must be the same data type. In this case, it required upsizing to bigint. In the technical writers defense (defence), they call it out in the documentation but I grew up only reading documentation after I failed to natively grasp how to make something work. The data type for stop must match the data type for start. SELECT * FROM GENERATE_SERIES(CAST(1 AS bigint), @duration, CAST(1 AS bigint)) AS Fails;

Key take away

Unlike any other situation I can think of off the top of my head, GENERATE_SERIES won't automatically promote/upsize/expand data types.