SQL Server Portal

I came across this error message in SQL Server 2012 using Format function, after that I did some research about this error message and came across that this error was part of SQL Server since 2005 or may be earlier than that. But the method to fix this error is same across the versions. Given below are the details & fixes of this error.

Message Number: 8116

Severity : 16

Error Message: Argument data type %ls is invalid for argument %d of %ls function.

Error Generation:
In the following example, I have date in varchar format and I am trying to format it into ARABIC culture using Format function.
Given below is the script.

USE tempdb
GO
DECLARE @Varchar_Date AS varchar(11)
SET @Varchar_Date='2013-12-12'
SELECT FORMAT (@Varchar_Date,'D','ar-SA') AS [Arabic Culture]
GO
--OUTPUT

Msg 8116, Level 16, State 1, Line 3
Argument data type varchar is invalid for argument 1 of format function.

Ooopps…… I cannot convert the date into Arabic format.

Resolution:
Basically, the reason for this error is not Format function exactly.  You can come across this error in any built-in function which accepts any argument(s). The resolution to this error is that you need to check the syntax of that particular function and see what type of data type it accepts, in which which argument(s). In our case it is Format function, it accepts three arguments and the first argument must be either Number or date time but we passed as varchar. So we need to change this varchar data type to datetime  data type and pass it to Format function.
Given below is the correct script.

USE tempdb
GO
DECLARE @Varchar_Date AS varchar(11)
DECLARE @Date AS datetime
SET @Varchar_Date='2013-12-12'
Set @Date=CONVERT(datetime,@Varchar_Date)
SELECT FORMAT (@Date,'D','ar-SA') AS [Arabic Culture]
GO
--OUTPUT

Arabic Culture
————–
09/صفر/1435

(1 row(s) affected)

Conclusion :
Remember, whenever you come across this error, you must consider the argument(s)’s data types of the function and whatever data types it accepts, you must pass exactly the same data type to that particular function.

Let me know if you came across this error and fixed it in a different way.