Query Using CONTAINS and DATEPART

  • I have a column(daysWorked) in the installers table that includes a value like "1,2,3,4,5" (without quotes). I am trying to show a schedule when the installer is on or off.

    I ran the following command.

    SELECT CASE WHEN CONTAINS(daysWorked, DATEPART(dw, GetDate()) THEN 'ON' ELSE 'OFF') AS iAvailable FROM Installers

    I receive the following error:

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near 'DATEPART'.

  • Is this what you are looking for

    CREATE TABLE #Installers(id INT,daysWorked INT)

    INSERT INTO #Installers

    SELECT 1,5 UNION ALL

    SELECT 2,4 UNION ALL

    SELECT 3,3 UNION ALL

    SELECT 4,0

    SELECT id, CASE

    WHEN (daysWorked = DATEPART(dw, GetDate())) THEN 'ON'

    ELSE 'OFF'

    END isAvailable

    FROM #Installers

    Results:

    1 ON

    2 OFF

    3 OFF

    4 OFF

    Please note that you are more likely to recieve assistance if you post table definition, sample data and expected results as per the first link in my signature block. It helps those who want to help you....

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • This is what I ended up coming up with and it returns the correct data but I have been told that using "LIKE" is a "no-no".

    SELECT iAvailable = CASE WHEN daysWorked like '%' + Convert(varchar,DatePart(dw, GetDate())) + '%' THEN 'ON' ELSE 'OFF' END FROM Installers

  • Jonathan Turlington

    Not knowing your actual table structure / indexes etc., etc. My temp table did not have an index, so my code would run using a table scan ... not very efficient to say the least.

    Run your code on a development DB and using SSMS select on the menu to show the Actual Execution Plan.

    Run my suggestion and do the same ... this should allow you to determine which bit of code is most efficient -- then go with that one.

    Use of LIKE has it's place, but generally (It depends) results in not being able to efficiently use indexes on a table. For your situation as I said above, test them, then make your decision.

    If you run the selected T-SQL frequently you may want to add an index, or a covering index to make it run more efficeintly --

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

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

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