none
Change format from SQL Variant to a numericvalue

    Question

  • I am trying to create a new view. One of the columns is a SQL Variant and I am trying to format to an integer (currently I cannot filter the records using numeric values, all records are numeric). Whenever I use the format function I get an execution error stating that FORMAT is not a recognized built in function.

    How can I convert this column so I can select records?

    1VAR256 is the name of the field I am trying to convert

    Bruce

    Thursday, October 24, 2013 4:26 PM

Answers

  • Whats your SQL Server version> Format works only in SQL Server 2012.

    Are you looking for the below:

    Declare @s sql_variant
    Set @s = 12.23
    Select cast(@s  as int)

    But, you can still filer with numeric data.

    Declare @s sql_variant
    Set @s = 12.23
    Select cast(@s  as numeric(10,2))


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by BKreft Thursday, October 24, 2013 6:23 PM
    Thursday, October 24, 2013 4:42 PM

All replies

  • Whats your SQL Server version> Format works only in SQL Server 2012.

    Are you looking for the below:

    Declare @s sql_variant
    Set @s = 12.23
    Select cast(@s  as int)

    But, you can still filer with numeric data.

    Declare @s sql_variant
    Set @s = 12.23
    Select cast(@s  as numeric(10,2))


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by BKreft Thursday, October 24, 2013 6:23 PM
    Thursday, October 24, 2013 4:42 PM
  • SQL version 2008

    I used your suggestion but I received the following statement: Use of CONVERT function might be unnecessary. It runs as expected. Here is the code:

    SELECT     TOP (100) PERCENT ID, Timestamp, CAST([1VAR256] AS Numeric(6, 0)) AS New
    FROM         dbo.[1TL10]
    WHERE     (CAST([1VAR256] AS Numeric(6, 0)) = 1)

    Bruce


    • Edited by BKreft Thursday, October 24, 2013 6:22 PM
    Thursday, October 24, 2013 5:25 PM