asked on

SQL date range

I am very new to SQL. I need a SQL statement for the proper way to do a date range. Below is what is currently in place. I need it to run for the previous weeks Sunday-Saturday

WHERE
B.CANCEL.DATE = CURRENT_DATE

My Best Guess

WHERE
B.CANCEL.DATE = >=CURRENT_DATE-8
AND B.CANCEL.DATE = <+CURRENT_DATE-2

5teveo

Here is some logic to find last last weeks Saturday and Sunday... Questions still exists about data scenarios that can occur. I choose to pick a Saturday and logic backs up to prior Saturday (just guessing)

declare @date1 datetime;
set @date1='2014-07-26'
--Saturday Prev
select DATEADD(WEEK, DATEDIFF(WEEK, 0, @date1), 0) + 5 - 7
generates 2014-07-19
--Sunday Prev
select DATEADD(WEEK, DATEDIFF(WEEK, 0, @date1), 0) + 6 - 7
generates 2014-07-20

Change date and watch logic and then use in where clause or select? as needed

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

It is possible to make those calculations into your own functions, e.g.

    CREATE FUNCTION `PrevWkStartingSunday`() RETURNS DATETIME  
    
      /* determines Sunday that begins the previous week
         if current_date weekday is 6 (sunday) deduct 7, 
         otherwise deduct the weekday plus 8 from the current date */
    
      RETURN DATE_SUB(CURRENT_DATE, INTERVAL (IF(WEEKDAY(CURRENT_DATE)=6,7,WEEKDAY(CURRENT_DATE)+8)) DAY)
    ;
    
    CREATE FUNCTION `PrevWkEndingSunday`() RETURNS DATETIME  
    
      /* determines Sunday that begins the previous week
         if current_date weekday is 6 (sunday) deduct 7, 
         otherwise deduct the weekday plus 8 from the current date 
         then add 7 days to get the following Sunday */
    
      RETURN DATE_ADD(DATE_SUB( CURRENT_DATE , INTERVAL (if(WEEKDAY( CURRENT_DATE )=6,7,WEEKDAY( CURRENT_DATE )+8)) DAY), INTERVAL 7 DAY)
    ;
    
    
    
    

**Query 1**:

    select
          date_format(dt ,'%a, %d/%M/%Y') as dt
        , PrevWkStartingSunday()
        , PrevWkEndingSunday()
    from tbl
    where dt >= PrevWkStartingSunday()
      and dt <  PrevWkEndingSunday()
    
    

**[Results][2]**:
    
    |                DT |      PREVWKSTARTINGSUNDAY() |        PREVWKENDINGSUNDAY() |
    |-------------------|-----------------------------|-----------------------------|
    | Sun, 13/July/2014 | July, 13 2014 00:00:00+0000 | July, 20 2014 00:00:00+0000 |
    | Mon, 14/July/2014 | July, 13 2014 00:00:00+0000 | July, 20 2014 00:00:00+0000 |
    | Tue, 15/July/2014 | July, 13 2014 00:00:00+0000 | July, 20 2014 00:00:00+0000 |
    | Wed, 16/July/2014 | July, 13 2014 00:00:00+0000 | July, 20 2014 00:00:00+0000 |
    | Thu, 17/July/2014 | July, 13 2014 00:00:00+0000 | July, 20 2014 00:00:00+0000 |
    | Fri, 18/July/2014 | July, 13 2014 00:00:00+0000 | July, 20 2014 00:00:00+0000 |
    | Sat, 19/July/2014 | July, 13 2014 00:00:00+0000 | July, 20 2014 00:00:00+0000 |



  [1]: http://sqlfiddle.com/#!9/a28ce/1

Open in new window

garyrobbins

ASKER

I've requested that this question be deleted for the following reason:

No suitable answers

PortletPaul

There was no suitable feedback either. Complete silence from your end.

Was it MySQL? I don't know to this day.

Take a look, there's 2 answers, but no indication from you why they are not suitable.

garyrobbins

ASKER

Thank You very much for the Feedback,

I didn't realize the importance of selecting and grading a solution accurately. Still learning the appropriate etiquette on this sight, I was just trying to clear the Question. I will pay attention to that in the future. Again Thank You for your time and feed back it was very thorough.