SQL: "Cannot construct data type date" when comparing two dates

sql examples
sql software
sql commands
w3schools sql
sql download
sql stands for
learn sql
sql tutorial pdf

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.

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

SQL Introduction, SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. It's Never Too Late to Learn a New Skill! Learn to Code and Join Our 45+ Million Users. Enjoy Extra Quizzes & Projects and Exclusive Content. Practice with Our App. Enroll Today!

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
)

Lesson 1: What is SQL?, Learn SQL - a language used to communicate with databases using SQL and learn how to write SQL queries. Online. On Demand. Learn at your own pace by completing interactive exercises.

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')

Is SQL a programming language?, Structured Query Language - or SQL, is a language that communicates with databases. Learn Duration: 4:26 Posted: Apr 16, 2019 SQL is a standard language for storing, manipulating and retrieving data in databases. Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.

Why is SQL the most important skill to learn?, SQL stands for Structured Query Language. 5 comments.Duration: 2:05 Posted: Nov 16, 2014 Get started on Azure SQL, the family of SQL cloud databases that provide flexible options for application migration, modernization, and development. Built on the same SQL Server engine, easily rehost onto SQL Server 2019 on Azure Virtual Machines using preconfigured images on Linux and Windows, or modernize on the latest SQL Server version with fully-managed Azure SQL Database or Azure SQL Managed Instance.

How Long Does it Take to Learn SQL in 2020?, Learn how to use SQL to store, query, and manipulate data. SQL is a special-​purpose programming language designed for managing data in a relational  SQL Server is the least vulnerable database for six years running in the NIST vulnerabilities database. In-database advanced analytics Analyze data directly within the SQL Server database—without moving the data—using R, the popular statistics language.

SQL, Real-world relational databases have tables that contain fields, constraints, and triggers, and tables are related through foreign keys. SQL is used  We live in a data-driven world: people search through data to find insights to inform strategy, marketing, operations, and a plethora of other categories. There are a ton of businesses that use large, relational databases, which makes a basic understanding of SQL a great employable skill not only for data scientists, but for almost everyone.

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