Post reply

Cast-Convert question

  • October 12, 2005 at 3:15 am

    #166458

    I have a decimal(8,0) field that I need to break into 2 parts, for example 10005555 will need to be 1000 decimal(4,0) and in another field 5555 decimal(4,0). I can use the cast and substring function but how do I get this back to decimal data Type? Thanks in advance

    Walter

  • October 12, 2005 at 4:49 am

    #596749

    DECLARE @Value DECIMAL(8,0)

    SELECT @Value = 10005555

    SELECT CAST(SUBSTRING(CAST (@Value AS VARCHAR(8)),1,4) AS DECIMAL(4,0)) AS FirstHalf,

     CAST(SUBSTRING(CAST (@Value AS VARCHAR(8)),5,4) AS DECIMAL(4,0)) AS SecondHalf

    HTH

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 12, 2005 at 5:40 am

    #596756

    Thanks so much for the help that worked for me

    Walter

     

     

  • October 12, 2005 at 5:43 am

    #596758

    How bout something like

    DECLARE @val DECIMAL(8, 0)

    DECLARE @Table TABLE

    (FirstHalf  DECIMAL(4, 0),

     SecondHalf DECIMAL(4, 0))

    SET NOCOUNT ON

    SET     @val = 10005555

    INSERT INTO @Table (FirstHalf, SecondHalf)

    SELECT LEFT(@Val, 4) FirstHalf, RIGHT(@Val, 4) SecondHalf

    SELECT FirstHalf, SecondHalf,

        CAST(CAST(FirstHalf AS CHAR(4)) + CAST(SecondHalf AS CHAR(4)) AS DECIMAL(8, 0)) PutBackTogether

      FROM @Table

    SET NOCOUNT OFF

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • October 13, 2005 at 6:47 am

    #597055

    Why do all the conversions:

    DECLARE @val decimal(8,0)

    DECLARE @val1 decimal(4,0)

    DECLARE @val2 decimal(4,0)

    set @val=10005555

    set @val1 = floor(@val/10000)

    set @val2 = @val-(@val1*10000)

    print 'V1:'+cast(@val1 as char(4))

    print 'V2:'+cast(@val2 as char(4))

    V1:1000   

    V2:5555

     

     

     

  • October 13, 2005 at 6:56 am

    #597057

    Walter, are all the values more than 4 digits? If not, then you will get an error with the conversion. To be safe, you could use one of the following instead:

    SELECT CONVERT(decimal(4,0), Left(Right('00000000' + CONVERT(varchar(8), @Value), 8), 4)) AS FirstHalf

         , CONVERT(decimal(4,0), Right(Right('00000000' + CONVERT(varchar(8), @Value), 8), 4)) AS SecondHalf

    or

    SELECT CONVERT(decimal(4,0), CONVERT(int, @value) / 10000) AS FirstHalf

         , CONVERT(decimal(4,0), CONVERT(int, @value) % 10000) AS SecondHalf

     

  • October 13, 2005 at 7:03 am

    #597061

    Yes the orginal value is 8,I will try that way as well. The code that Gila put here works as well. So I am going to try to put this all together in a view. The end result should be a daily revenue report by department. The first 4 digits are the dept and the last 4 is the type of revenue. Y'all have really helped me on this, many thanks

    Walter

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply