Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • About
  • Toggle search form

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

Posted on March 7, 2022January 1, 2024 By No Comments on 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!

Sql Server, Sql Server 2016 Tags:datefromparts, error, sql server

Post navigation

Previous Post: Run an old Apache ASP web application in Ubuntu 20.04
Next Post: Install and old Kindle app version in an Android 7 tablet

Related Posts

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine Excel
SQL Server and SARGables predicates Sql Server
Delete all rows from all tables in a Sql Server database Sql Server
Get SQL Server version in T-SQL Sql Server
Sql Server: apply an aggregate function to fields values in a row Sql Server
Search a string in stored procedures, functions and views in a SQL Server database Sql Server

Leave a Reply Cancel reply

Please do not insert personal or sensitive data in the comment.


Search

Related posts

  • Sql Server error: A severe error occurred on the…
  • Sql Server: apply an aggregate function to fields…
  • Load a huge amount of data in a Sql Server table
  • Sql Server error using a database with a CLR assembly

Categories

  • .NET 6 (1)
  • .NET 7 (1)
  • AJAX (1)
  • Android (2)
  • Apache (4)
  • ASP.NET (9)
  • ASP.NET MVC (3)
  • Avalonia UI (1)
  • BCP (1)
  • Bitlocker (2)
  • C# (14)
  • CentOS (4)
  • ClosedXML (1)
  • CLR (1)
  • DNS (1)
  • Encryption (3)
  • Excel (2)
  • FuelPHP (3)
  • Games (2)
  • Google Chrome (1)
  • GSuite (1)
  • HTML (1)
  • Imagick (2)
  • Javascript (1)
  • Kindle (1)
  • LAMP (3)
  • Linux (7)
  • MariaDB (2)
  • Mathematics (2)
  • MySql (4)
  • NPOI (1)
  • Office 365 (1)
  • Perl (1)
  • PHP (6)
  • Programming (1)
  • Remote desktop (1)
  • SFTP (2)
  • Sockets (3)
  • Sql Server (20)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (4)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • VueJS (1)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (6)

Copyright © 2024 The Dummy Programmer | Privacy Policy | Terms of use |

Powered by PressBook Masonry Dark

Manage Cookie Consent
This site doesn’t collect user personal data and doesn’t install profiling or analytical cookies, either its own or from third parties. Read our privacy policy for more info.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage {vendor_count} vendors Read more about these purposes
View preferences
{title} {title} {title}