Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Talking Unanswered: Casting varchars to bigint

    Hello !


    I am getting the follwing error:

    Server: Msg 8114, Level 16, State 5, Procedure usp_import_CLIS, Line 170
    Error converting data type varchar to bigint.


    I am trying to convert al varchars to the follwing

    insert into DQ_Babel.dbo.tbl_CLI_authorthenticate
    (block, cli_start, cli_end, allow)
    select
    cast(block as int),
    cast(cli_start as bigint),
    cast(cli_end as bigint),
    cast([rule] as int)
    from
    tbl_CLI_UPLOAD


    Anyone any ideas?

    Thanks Mel

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Casting varchars to bigint

    Originally posted by melaniemayfield
    Hello !


    I am getting the follwing error:

    Server: Msg 8114, Level 16, State 5, Procedure usp_import_CLIS, Line 170
    Error converting data type varchar to bigint.


    I am trying to convert al varchars to the follwing

    insert into DQ_Babel.dbo.tbl_CLI_authorthenticate
    (block, cli_start, cli_end, allow)
    select
    cast(block as int),
    cast(cli_start as bigint),
    cast(cli_end as bigint),
    cast([rule] as int)
    from
    tbl_CLI_UPLOAD


    Anyone any ideas?

    Thanks Mel
    How about the DDL for thos tables..

    oft hand I'd say you've got non numeric data in those fields...

    DO This and see what you get...

    SELECT * FROM tbl_CLI_UPLOAD
    WHERE ISNUMERIC(block)=0
    OR ISNUMERIC(cli_start)=0
    OR ISNUMERIC(cli_end)=0
    OR ISNUMERIC([rule])=0

    Anything in that result set are the offending rows...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If your values have decimal points, you will need to perform an intermediary cast before converting them to bigint:

    --Doesn't work
    declare @MyVarchar Varchar(15)
    set @MyVarchar = '1.000'
    print cast(@MyVarchar as bigint)

    --Works good
    declare @MyVarchar Varchar(15)
    set @MyVarchar = '1.000'
    print cast(Cast(@MyVarchar as decimal(9,0)) as bigint)

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •