CODEWITHSUNDEEP
sql-servert-sqldatesqldatatypes
John Ortt
John Ortt

Reputation: 25

Some of the arguments have values which are not valid. - Date Issue in TSQL

I know this has been asked before but I have checked the existing solutions and I can't find the issue. For example there are no dates before 2000-01-01 or after 2019-12-24. In addition all dates are in YYYY-MM-DD to remove ambiguity.

The code below runs fine, but when I remove the comments on the second to last line (for the criteria) I get the error:

"Cannot construct data type date, some of the arguments have values which are not valid."

. This suggests that the problem is with either StartDate or EndDate but I cannot see an issue.

I would really appreciate someone helping me to get past this blocker.
(The code is completely self contained using derived tables).

Thanks in advance

Declare @StartDate AS datetime;
Declare @EndDate AS datetime;

Set @StartDate = '2017/06/01';
Set @EndDate = '2018/05/01';

WITH theIntegers AS 
(SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n AS theInteger 
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
     WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n >0),

theWeeks AS
(SELECT theInteger AS Wk FROM theIntegers
WHERE theInteger > 0 AND theInteger < 53),

theYears AS
(SELECT theInteger AS Yr FROM theIntegers
WHERE theInteger > 2000 AND theInteger < 2020),

theDates AS
(SELECT Wk,Yr,  DATEADD(WEEK,Wk-1,DATEFROMPARTS(Yr,1,1)) AS STDT
FROM theWeeks, theYears)

SELECT * FROM theDates
--WHERE StDt BETWEEN @StartDate AND @EndDate 
ORDER BY StDt DESC

Upvotes: 1

Views: 1461

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

SQL Server combines the where clauses from all parts of your query to one place in the execution plan, the left most Nested Loops Join operator.

The predicate looks like this from an estimated query plan.

dateadd(week,
        [Union1010]+(10)*[Union1021]+(100)*[Union1032]+(1000)*[Union1043]-(1),
        datefromparts([Union1055]+(10)*[Union1066]+(100)*[Union1077]+(1000)*[Union1088],
                      (1),
                      (1)))>= [@StartDate] AND 
dateadd(week,
        [Union1010]+(10)*[Union1021]+(100)*[Union1032]+(1000)*[Union1043]-(1),
        datefromparts([Union1055]+(10)*[Union1066]+(100)*[Union1077]+(1000)*[Union1088],
                      (1),
                      (1)))<=[@EndDate] AND 
([Union1055]+(10)*[Union1066]+(100)*[Union1077]+(1000)*[Union1088])>(2000) AND 
([Union1055]+(10)*[Union1066]+(100)*[Union1077]+(1000)*[Union1088])<(2020) AND 
([Union1055]+(10)*[Union1066]+(100)*[Union1077]+(1000)*[Union1088])>(0)

So SQL Server is trying to create date values for all rows coming out of theIntegers before filtering out the rows on year and week and that makes your query trying to create dates with week numbers larger than 52.

You can fix your query by using top(52) in theWeeks CTE.

theWeeks AS
(SELECT TOP(52) theInteger AS Wk FROM theIntegers order by theInteger),

Upvotes: 1

Mike Miller
Mike Miller

Reputation: 16575

I can't explain it, but I have got it to work... I assume it's a one-off and do with optimization.

I've spun out the date creation to a function...

CREATE FUNCTION createDate(@wk int, @yr int)
RETURNS datetime
AS
begin
declare @dt datetime
set @dt = (select DATEADD(WEEK,@wk-1,DATEFROMPARTS(@yr,1,1)))
return @dt
end;

Then changed the query

Declare @StartDate AS datetime;
Declare @EndDate AS datetime;

Set @StartDate = '2017/06/01';
Set @EndDate = '2018/05/01';

WITH theIntegers AS 
(SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n AS theInteger 
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
     WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n >0)

,theWeeks AS
(SELECT theInteger AS Wk FROM theIntegers
WHERE theInteger > 0 AND theInteger < 53),

theYears AS
(SELECT theInteger AS Yr FROM theIntegers
WHERE theInteger > 2000 AND theInteger < 2020)

,theDates AS
(SELECT Wk,Yr, dbo.createDate(wk,yr) AS STDT
FROM theWeeks, theYears)

SELECT * FROM theDates WHERE StDt BETWEEN @StartDate AND @EndDate  ORDER BY StDt DESC;

Upvotes: 0

Related Questions