Post reply

Recording multiple days of the month

  • March 5, 2004 at 8:39 am

    #107842

    On a form (in Access) I want to give users the ability by clicking on or off check boxes, the days of the month an extract can run e.g. 3rd, 13th, 23rd.

    I was thinking about sending this information to and from SQL Server as a 31 character string e.g. '10100101101001011010010110100101' and then converting this as a number for storage purposes. I would then need to convert this back to a string to populate the Access check boxes and also to use in an SQL Server job to determine whether a job should run or not.

    I can't find any reference for converting a binary string to a number and vica versa.

    Maybe I am trying to be to clever and should just record the binary number as a string.

  • March 5, 2004 at 9:24 am

    #497583

    DECLARE @str char(31) , @int int, @backToChar char(31)

    SET @str ='1010010110100101101001011010010' -- Only 31 Bits

    -- To Int

    SELECT @int = ISNULL(@int,0) + CASE WHEN Substring(@str,number,1) = '1' THEN power( 2, 31-number) ELSE 0 END

    FROM master..spt_values

    Where Type = 'P' and number between 0 and 30

    ORDER BY Number

    -- Back To Char

    select @backToChar =  Cast( CASE WHEN @int & power( 2, number) = 0 THEN '0' ELSE '1' END as char(1)) + ISNULL(@backToChar ,'')

    FROM master..spt_values

    Where Type = 'P' and number between 0 and 30

    ORDER BY Number

    SELECT @backToChar ,@str ,@int

    Woudn't it be simpler to save it as char(31) ?

     


    * Noel

  • March 5, 2004 at 9:38 am

    #497586

    OR If you can live with undocummented functions

    DECLARE @str char(32) , @int int, @backToChar char(32)

    SET @str ='10100101101001011010010110100101'

    select  @int = fn_replbitstringtoint(@str)

    select @backToChar = fn_replinttobitstring(@int)

    SELECT @backToChar ,@str ,@int


    * Noel

  • March 5, 2004 at 9:46 am

    #497587

    QUOTE

    Woudn't it be simpler to save it as char(31) ?

    UNQUOTE

    I have been thinking about it since writing my question, and I think that was the conclusion I came to. Sometimes one does not see the obvious!!!

Viewing 4 posts - 1 through 4 (of 4 total)

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