Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: IF statement in WHERE clause

    HI,

    Is it possible to use an IF statement in the WHERE clause?

    Basically I have query that lists orders over the last 5 days :
    Code:
    WHERE (order_date = @current_date OR
              order_date = DATEADD(dd, @current_date - 1) OR
              order_date = DATEADD(dd, @current_date - 2) OR
              order_date = DATEADD(dd, @current_date - 3) OR
              order_date = DATEADD(dd, @current_date - 4)  )
    How can I add an IF statement so that if @current_date is Monday then only lists orders for the Monday of that week. If @current_date is Tuesday then only list orders for the Monday and Tuesday of that week etc. In other words I only want to list orders for the days in the same week as @current_date.

    I've tried using a CASE statement but it comes up with an error message about the OR statement in the query:
    Code:
    WHERE order_date = 
         CASE WHEN DATEPART(dw, @current_date) = 1 THEN 
                    @current_date
                WHEN DATEPART(dw, @current_date) = 2 THEN   
                  @current_date OR 
                  order_date = DATEADD(dd, @current_date - 1)
                WHEN DATEPART(dw, @current_date) = 3 THEN
                  @current_date OR 
                   order_date = DATEADD(dd, @current_date - 1) OR
                   order_date = DATEADD(dd, @current_date - 2)
               WHEN DATEPART(dw, @current_date) = 4 THEN
                  @current_date OR 
                   order_date = DATEADD(dd, @current_date - 1) OR
                   order_date = DATEADD(dd, @current_date - 2) OR
                   order_date = DATEADD(dd, @current_date - 3)
              WHEN DATEPART(dw, @current_date) = 5 THEN
                  @current_date OR 
                   order_date = DATEADD(dd, @current_date - 1) OR
                   order_date = DATEADD(dd, @current_date - 2) OR
                   order_date = DATEADD(dd, @current_date - 3) OR
                   order_date = DATEADD(dd, @current_date - 4) 
           END
    Any other way to acheive this would be appreciated.

    Note: for the above query I've set DATEFIRST to 1 so that Monday is the first day of the week.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    WHERE order_date BETWEEN DateAdd(wk, DateDiff(wk, 0, @current_date), 0)
       AND @current_date
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Pat - what's changed re. your opinion of implicit conversion of integers to dates?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I post as the members of the forum post, I code as I code for my personal and professional code. In other words my opinion hasn't changed, I think using zero as a substitute for an arbitrary date is an abomination but I do it here because that's what the consensus for coding seems to be here.

    In my own code, I use explicit dates. I think that everyone ought to do that. I'm still willing to try to post code that looks the way that DBForums users appear to want their code formatted.

    When in Rome...

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Since 0 is a simply constant, and a magic one at that, I took your views on board and have now switched to coding the requirement like this:
    Code:
    DateAdd(wk, DateDiff(wk, '19000101', @current_date), '19000101')
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That's pretty much how I'd code it, but I use the ISO formatted dates like '1900-01-01' but you and I see eye-to-eye on this. Based on the code that I see posted, the posters here at DBForums seem to use and expect the zero so when I post code here I try to do the same. I feel that it is poor coding practice, it gives me the willies every time I write it because I know deep down that it is wrong, but it is what I usually see here so it is what I try to post.

    There are a lot of things that I do as regular coding practice that would probably freak people out. I almost always put constants on the left of comparisions, I always use UTC time and date values, I have a strong preference for GUIDs... I have a strong coding style that has developed over decades of coding. Every habit has a reason, and I remember the vast majority of those reasons. I don't make everybody code "my way", that would be rude.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't use the date-only-ISO formatted date because it can fail for a British connection. '1900-01-13' would have to instead be '1900-01-13T00:00:00' which is a PITA, however in principle I agree with its usage!

    We will differ on your last point though. I think it is the height of rudeness when other people do not subscribe to my coding style.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •