SQL Server Portal

SQL Server 2012 is equipped with many new features and functions. The same ways it is equipped with its new error messages as well. In this article we will discuss one of the new error messages (Cannot construct data type %ls, some of the arguments have values which are not valid) introduced in SQL SERVER 2012.

Let’s discuss it in detail:

Error Number: 289

Error Message: “Cannot construct data type %ls, some of the arguments have values which are not valid.”

Error Generation:
Let me create few examples to generate this error:

Declare @Year as int=2012
Declare @Month as int = 12
Declare @Day as int =32 --Invalid Day
Declare @Hour as int=25 --Invalid Hour
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50

Example 1:

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--OUTPUT

Msg 289, Level 16, State 1, Line 9
Cannot construct data type date, some of the arguments have values which are not valid.

Example 2:

Select TIMEFROMPARTS(@Hour,@Minute,@Second,@Fraction,2) as [Result]
--OUTPUT

Msg 289, Level 16, State 2, Line 15
Cannot construct data type time, some of the arguments have values which are not valid.

Example 3:

Select SMALLDATETIMEFROMPARTS(@Year,@Month,@Day,@hour,@Minute) AS [RESULT]
--OUTPUT

Msg 289, Level 16, State 4, Line 21
Cannot construct data type smalldatetime, some of the arguments have values which are not valid.

Example 4:

Select DATETIME2FROMPARTS( @Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,2) as [RESULT]
--OUTPUT

Msg 289, Level 16, State 5, Line 27
Cannot construct data type datetime2, some of the arguments have values which are not valid.

Resolution:
In the above examples, you can see, I passed an invalid argument (day cannot be 32 in a month & hour cannot be 25 in a day) to above functions. So the constructor of this function cannot create a date from these arguments, so it generates the error.

Whenever you come across this error, please check the arguments passed in the function. The arguments must be valid to avoid this error.

Please let me know if you have other samples to regenerate such error.