SQL Server Error Messages - Msg 10760¶

Error Message¶

Server: Msg 10760, Level 16, State 1, Line 1
Scale argument is not valid.
Valid expressions for data type <Data Type> scale argument are integer constants and integer constant expressions.

Causes¶

SQL Server 2012 introduced a few functions that return date and time values from their parts. Three of these new date/time functions are the DATETIME2FROMPARTS, DATETIMEOFFSETFROMPARTS and TIMEFROMPARTS which includes a TIME part as part of their return value.

The DATETIME2FROMPARTS date function returns a DATETIME2 value for the specified date and time and with the specified precision. The DATETIMEOFFSETFROMPARTS date function returns a DATETIMEOFFSET value for the specified date and time and with the specified offsets and precision. The TIMEFROMPARTS date function returns a TIME value for the specified time and with the specified precision. The syntax for each of these date functions are as follows:

DATETIME2FROMPARTS ( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <precision> ) 
DATETIMEOFFSETFROMPARTS ( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, 
                          <hour_offset>, <minute_offset>, <precision> )
TIMEFROMPARTS ( <hour>, <minute>, <seconds>, <fractions>, <precision> )

Passing a NULL value to any of these parameters, with the exception of the <precision> parameter, will yield a NULL value. On the other hand, passing a NULL value in the <precision> parameter will raise this error message.

Here are a couple of examples of how this error message can be generated:

SELECT TIMEFROMPARTS( 8, 0, 0, 0, NULL) AS [SchoolStartTime]

Msg 10760, Level 16, State 1, Line 1
Scale argument is not valid.
Valid expressions for data type time scale argument are integer constants and integer constant expressions.

SELECT DATETIME2FROMPARTS ( 2015, 1, 1, 0, 0, 0, 0, NULL ) AS [HappyNewYear]

Msg 10760, Level 16, State 1, Line 2
Scale argument is not valid.
Valid expressions for data type datetime2 scale argument are integer constants and integer constant expressions.

Solution / Work Around¶

As mentioned earlier, when using the DATETIME2FROMPARTS, DATETIMEOFFSETFROMPARTS and TIMEFROMPARTS date functions, a value must be passed to the parameter. Valid values are from 0 to 7. Passing a value outside the range of 0 to 7 will yield the following error message:

SELECT TIMEFROMPARTS( 8, 0, 0, 0, 8) AS [SchoolStartTime]

Msg 1002, Level 16, State 2, Line 1
Line 1: Specified scale 8 is invalid.

A value of 0 can be passed to the <precision> parameter as long as the value passed to the <fractions> parameter is 0 as well. Passing a 0 <precision> with a non-0 <fractions> will raise the following error message:

SELECT DATETIME2FROMPARTS ( 2015, 1, 1, 0, 0, 0, 1, 0 ) AS [HappyNewYear]

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

If the fraction part of the time is not needed, the DATETIMEFROMPARTS or the SMALLDATETIMEFROMPARTS can be used instead. The DATETIMEFROMPARTS returns a DATETIME value for the specified date and time while the SMALLDATETIMEFROMPARTS returns a SMALLDATETIME value for the specified date and time. The syntax for these 2 date functions are as follows:

DATETIMEFROMPARTS ( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <milliseconds> )
SMALLDATETIMEFROMPARTS ( <year>, <month>, <day>, <hour>, <minute> )

Unfortunately, as of this writing, putting the code within a TRY ... CATCH error handling will not trap the error. As can be seen from the following script, the error is still raised instead of being handled by the TRY … CATCH error handler.

DECLARE @HappyNewYear   DATETIME2

BEGIN TRY
    SET @HappyNewYear   = DATETIME2FROMPARTS ( 2015, 1, 1, 0, 0, 0, 0, NULL )
END TRY
BEGIN CATCH
    SET @HappyNewYear   = NULL
END CATCH

Msg 10760, Level 16, State 1, Line 4
Scale argument is not valid.
Valid expressions for data type datetime2 scale argument are integer 
constants and integer constant expressions.