Getting RGB(R,G,B) from ARGB integer (SQL)

Question

I need to display some data in an SSRS 2008r2 report and the colors have to match a Windows VB app that saves it's colors as integers (e.g.16744703 is a pinkish color). I believe this is ARGB format. I'm not concerned about the alpha value, as the application does not allow the user to modify it.

I'm stuck on the SQL to convert ARGB to something compatible in SSRS. I need to do the translation in SQL as there are other factors that may override an objects color.

I can work with 3 ints for rgb or a hex value

Anyone got any idea how tot do this?

Regards

mark

Answer 1

Figured it out. Here's a function that returs either RGB() or Hex

   -- Description:  Converts ARGB to RGB(RR,GG,BB)
   --               e.g. 16744703 returns RGB(255,128,255) or #FF80FF   
   CREATE FUNCTION [dbo].[ARGB2RGB] 
    (
        @ARGB AS BIGINT 
       ,@ColorType AS VARCHAR(1)   -- 'H' = Hex, 'R' = RGB
    )
    RETURNS VARCHAR(16)
    AS
    BEGIN
        DECLARE @Octet1 TINYINT
        DECLARE @Octet2 TINYINT
        DECLARE @Octet3 TINYINT
        DECLARE @Octet4 TINYINT
        DECLARE @RestOfColor BIGINT

        SET @Octet1 = @ARGB / 16777216
        SET @RestOfColor = @ARGB - ( @Octet1 * CAST(16777216 AS BIGINT) )
        SET @Octet2 = @RestOfColor / 65536
        SET @RestOfColor = @RestOfColor - ( @Octet2 * 65536 )
        SET @Octet3 = @RestOfColor / 256
        SET @Octet4 = @RestOfColor - ( @Octet3 * 256 )

        RETURN
            CASE @ColorType
              WHEN 'R'
              THEN 'RGB(' + CONVERT(VARCHAR, @Octet4) + ','
                   + CONVERT(VARCHAR, @Octet3) + ',' + CONVERT(VARCHAR, @Octet2)
                   + ')'
              WHEN 'H'
              THEN '#' + RIGHT(sys.fn_varbintohexstr(@Octet4), 2)
                   + RIGHT(sys.fn_varbintohexstr(@Octet3), 2)
                   + RIGHT(sys.fn_varbintohexstr(@Octet2), 2)
            END 
    END

Hope someone else finds it useful

Regards

Mark