Sql Server error: Cannot construct data type date, some of the arguments have values which are not valid.

Imagine waking up on a cold winter morning, and receiving an error from your customer that reads as follows:

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

Apart from the error message and the web page in which it occurred, I didn’t have much other information. I don’t even have physical access to the servers the application runs on, so I had to do an analysis trying to reproduce the error in my local environment.

So I started digging into the code of the page that was reporting the error and finally got my eye on the following statement in a Sql Server stored procedure:

SELECT	DATEFROMPARTS(D.YEARSTART, 1, 1),
	DATEFROMPARTS(D.YEAREND, 12, 31),
FROM CV_DEGREES D
WHERE D.ID = @ID

And the error was exactly there…

The DATEFROMPARTS function throws an exception (the one we are talking about) if the parameters passed are not in the expected range. In my case the problem was the year parameter, for which the allowed range is 1 – 9999.

Someone was able to enter a degree start date equal to zero, and this caused the error in the query above.

To solve the problem I have to find a way to clean the data, but for now a quick and dirty solution is change the query as follow:

SELECT	DATEFROMPARTS(CASE WHEN D.YEARSTART < 1 THEN 1 WHEN D.YEARSTART > 9999 THEN 9999 ELSE D.YEARSTART END, 1, 1),
	DATEFROMPARTS(CASE WHEN D.YEAREND < 1 THEN 1 WHEN D.YEAREND > 9999 THEN 9999 ELSE D.YEAREND END, 12, 31),
FROM CV_DEGREES D
WHERE D.ID = @I

Hope this helps.

See you soon!