SQL FORMAT function error

Question

This SQL:

select FORMAT(lNum,'##-###-##-###') 
from  [rpt].[myView]

Produces the following error:

Argument data type varchar is invalid for argument 1 of format function.

lNum is a varchar(10)

Running SQL Server 2012

Keep in mind, FORMAT can be an expensive function. See: http://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but — Joe Stefanelli
– Joe Stefanelli, Commented Dec 4, 2015 at 17:57

Vamsi Prabhala · Accepted Answer · 2015-12-04 18:01:26Z

20

varchar isn't supported as the first argument to FORMAT. The only categories of datatypes supported are Date and Time and Numeric.

You could do

select FORMAT(cast(lNum as numeric),'##-###-##-###') from  [rpt].[myView]

From levelonehuman's comment : Documentation

answered Dec 4, 2015 at 17:52

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Instead of duplicating the answer, FORMAT on MSDN