Post reply

Week day

  • September 12, 2002 at 7:44 pm

    #79307

    Hi,

    I need help regarding a week day stuff.

    Basically, I would like to return a list of the patients who will have appointmnet in the next 5 working days (means Sat and Sun won't be counted). Could you please help me how to write this in T-SQL in view?

    Thanks.

    Regards,

    Anton

  • September 13, 2002 at 5:23 am

    #436477

    First you need to get the date without time or at least starting at miidnight so that can be easiest done with CONVERT (Ex. CONVERT(varchar, GETDATE(), 101) returns format mm/dd/yyyy 00:00:00 see BOL for other options).

    Next you need to set the date range so here we will use BETWEEN and use DATEADD to add 1 day to our current day and 7 to our end date (we know SAT and SUN can be at any poisiton so we need to account for the 5 working days plus Sat and Sun to make sure we conver.

    Now to remove the output of Sat and Sun we use DATEPART to get the day of the week value which for Sunday by default is 1 an Saturday is 7.

    Here is what your query should look like in the Where clause.

    WHERE (Schd BETWEEN DATEADD(d,1,CONVERT(varchar,GETDATE(),101) AND DATEADD(d,7,CONVERT(varchar,GETDATE(),101)) AND DATEPART(dw, Schd) NOT IN (1,7)

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • September 14, 2002 at 2:03 am

    #436545

    thanks Antares686.

    Can this SQL Statement work with ORACLE or mySQL? If don't, how we rewrite this script then?

    Once again thanks for your help

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

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