0

I'm having an issue in a query where SQL Server is throwing the error

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

when comparing two date objects that themselves are valid.

If I remove the where clause, it resolves without error, but the moment I try to compare them with any relational or equality operator it errors again.

Minimum query to reproduce the issue is as follows:

with Years as 
(
    select 
        YEAR(getdate()) + 1 Year, 
        DATEFROMPARTS(YEAR(getdate()) + 1, 1, 1) FirstOfTheYear, 
        0 YearOffset
    union all
    select 
        Year - 1,
        DATEFROMPARTS(Year - 1, 1, 1),
        YearOffset + 1
    from Years
    where YearOffset < 5
),
Months as
(
    select 1 Month
    union all
    select Month + 1
    from Months
    where Month < 12
),
Days as 
(
    select 1 Day
    union all
    select Day + 1
    from Days
    where Day < 31
), 
Dates as 
(
    select cast(DATEFROMPARTS(Year, Month, Day) as date) Date
    from Years
    cross join Months
    cross join Days
    where DAY(EOMONTH(FirstOfTheYear, Month - 1)) >= Day
)
select Dates.Date, cast ('2019-10-01' as date), CAST ('2019-10-11' as date)
from Dates
where Date = cast ('2019-10-01' as date) -- Comment this line out and the error goes away, occurs with any date construction pattern
--where Dates.[Date] >= datefromparts(2019, 10, 01) and Dates.[Date] <= DATEFROMPARTS(2019, 10, 11)
order by date

Commenting out the where clause returns results as expected, confirming that it is specifically the comparison that is triggering this issue.

Additionally, manually creating a handful of dates (first of the year, 2015-2019, the October dates in the query) and querying against that does not cause the error to show.

Edit: I want to emphasize that the code is already handling February and leap years correctly. The output of the Dates CTE is valid and outputs the full range without error. It is only when I reference the date in the where clause that it throws the error

Edit2: I was able to resolve my issue by switching to a different date generation pattern (adding a day, day by day, in a recursive), but I still am curious what causes this error.

Improve this question

3 Answers 3

Reset to default
1

The point of a couple of the other answers is that attacking the issue in the manner you are is not necessarily the most efficient way of generating a date's table. Most of the time when constrained with SQL server people will lead someone to use a Tally table for this purpose. Doing so will remain a SET based operation rather than requiring looping or recursion. Which means the recursion limit you mentioned in one of your comments simply doesn't apply.

A Tally table is a set of numeric values that you can then use to generate or produce the values you want. In this case that is approximately 1827 days (5 years + 1 day) but can differ by leap years. The leap years and February are likely the issues within your code. Anyway to generate a tally table you can start with 10 values then cross join till you get to an acceptable number of combinations. 3 cross joins will bring you to 10,000 values and ROW_NUMBER() - 1 can be used to generate a 0 based increment. After which you can use DATEADD() to actually create the dates:

;WITH cteTen AS (
    SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(n)
)

, cteTally AS (
    SELECT
        N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM
        cteTen t10
        CROSS JOIN cteTen t100
        CROSS JOIN cteTen t1000
        CROSS JOIN cteTen t10000 
)

, cteStartOfNextYear AS (
    SELECT
        StartOfNextYear = s.[Date]
        ,NumOfDaysBetween = DATEDIFF(DAY,DATEADD(YEAR,-5,s.[Date]),s.[Date])
    FROM
        (VALUES (DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1))) s([Date])
)

, cteDates AS (
    SELECT
        [Date] = DATEADD(DAY,- t.N, s.StartOfNextYear)
    FROM
        cteStartOfNextYear s
        INNER JOIN cteTally t
        ON t.N <= NumOfDaysBetween
)

SELECT *
FROM
    cteDates
ORDER BY
    [Date]

Per our conversation, I see why you would think that EOMONTH() would take care of the issue but it is an order of operations sort of. So the DATEFROMPARTS() portion is analyzed across the entirety of the dataset prior to interpreting the where clause. So it is trying to build the date of 29,30 of Feb. etc. before it is limiting it to the number of days defined by EOMONTH() where clause

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I don't know how many times I have to tell people that I already verified that the CTE is handling February and 30 day months without issue (verifiable by commenting out the WHERE clause). But I appreciate the breakdown of how a tally table works without recursion and gave a +1 for that.
@livingparadox maybe the question you should ask yourself is how many times do people need to tell you that February, 30 day months, and leap years are your issue? Take the same query you have above and change the 31 to 28 and the code works. So your issue clearly lies with February etc.
Well, changing 31 to 28 did get rid of the error even with the where clause at the end. Any idea why it only throws the error with the where clause but doesn't throw it when it it is absent?
@livingparadox yeah I was just looking at the execution plans too though because I see why you would think that EOMONTH() would take care of the issue but it is an order of operations sort of. So the DATEFROMPARTS() portion is analyzed accross the entirety of the dataset prior to interpreting the where clause. So it is trying to build the date of 29,30 of Feb. etc. before it is limiting it to the number of days defined by EOMONTH() where clause
Ah okay. Could you add that explanation to your answer? I've accepted your answer as you've both provided the explanation I was looking for and an alternative.
0

I have no idea why you are using code like that to generate days. Why not start at the first date and just add one date at a time?

In any case Feb 29 or 30 or 31 is going to cause an error. You can fix this approach by changing the dates subquery:

Dates as (
    select try_convert(date, concat(year, '-' month, '-', day)) as  Date
    from Years y cross join
         Months m cross join
         Days
    where try_convert(date, concat(year, '-' month, '-', day)) and
          DAY(EOMONTH(FirstOfTheYear, Month - 1)) >= Day
)
Improve this answer

1 Comment

The date generation code is to avoid having to muck with recursion limits, which I'd have to do to cover a full five years worth of dates. My code already handles February (included leap years) with the EOMONTH function, which gets the last day of the month for a specific month and year. As stated in my question, the query already runs without error if I leave off the WHERE clause; it is only when the dates are compared that it throws the error.
0

You're asking DATEFROMPARTS to convert invalid combinations of dates and times. That's what is throwing the error - not your CAST statement.

See Using T-SQL DATEFROMPARTS to return NULL instead of throw error to find your problem dates in general.

Your query creates dates including February 29th, 30th and 31st, as well as the 31st of April, June, September and November.

If you just want all the dates from 2015 through 2020, you can count off a bunch of days and add to a base date. SQL Server will handle the month issues for you:

--  Create up to 16 million integers
WITH N AS (SELECT 0 AS N FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7)) T(n))
,    M AS (SELECT 0 AS N FROM N A, N B, N C, N D, N E, N F, N G, N H)
,    Z AS (SELECT ROW_NUMBER() OVER (ORDER BY A.N) AS N FROM M A)

--  Filter only the integers you need; add to a start date
SELECT CAST(DATEADD(DAY, N-1, '2015-01-01') AS DATE) FROM Z
WHERE N < DATEDIFF(DAY, '2015-01-01', '2020-01-01')
Improve this answer

1 Comment

My code already handles February (included leap years) with the EOMONTH function, which gets the last day of the month for a specific month and year. As stated in my question, the query already runs without error if I leave off the WHERE clause; it is only when the dates are compared that it throws the error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.