Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • The Dummy Programmer Robot
    • Overview
    • Version history & downloads
    • Tutorials
      • TDP Robot – The basics
      • A simple SQL Server backup solution
      • A simple SQL Server backup solution – A next step
  • 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, 2022March 7, 2022 By

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

SQL Server LEN function return 0 passing a string with one space Sql Server
Sql Server: apply an aggregate function to fields values in a row Sql Server
Get SQL Server version in T-SQL Sql Server
Get all referencing tables of a table in Sql Server Sql Server
Delete all rows from all tables in a Sql Server database Sql Server
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine Excel

Search

Related posts

  • Sql Server error: A severe error occurred on the current…
  • Sql Server: apply an aggregate function to fields values in…
  • 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)
  • 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 (6)
  • 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 (18)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (2)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • The Dummy Programmer Robot (6)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (6)

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

Powered by PressBook Masonry Dark