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
garyrobbinsAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
I am assuming you use MySQL.
------
The simple answer is you were almost correct, a better method is:

WHERE    B.CANCEL.DATE >= CURRENT_DATE - 8
        AND B.CANCEL.DATE <  CURRENT_DATE - 1 /* just less than, but note the minus one */

the safest way to define a date range is to use a combination of  >= with < 

e.g. let's say you want everything for February 2014, this is the way to do it:

WHERE    B.CANCEL.DATE >= '2014-02-01' /* >= 1st of Feb */
        AND B.CANCEL.DATE <  '2014-03-01' /* but less than the first of March */

for more on this topic please see: "Beware of Between"

------------------------
Now, automating "the previous weeks Sunday-Saturday"

This is a bit more complex because we must use date functions. But remember we want to use the method above, which will mean:

WHERE B.CANCEL.DATE >= "previous week's Sunday"
    AND B.CANCEL.DATE <  "the Sunday after that"

i.e. because we use less than we also use Sunday, not Saturday, for the end date.
----

You already know a date function: CURRENT_DATE

we need 3 more date functions:
WEEKDAY() This function tells us which day of week using integers  (0 = Monday, 1 = Tuesday, … 6 = Sunday).
DATE_ADD() this allows us to add time intervals to a date/time value
DATE_SUB() which does the opposite (subtracts)

plue, we also need an IF() function so we can test if a date is Sunday then deduct 7 otherwise deduct a different value.

Putting these together we can get "previous week's Sunday" using this:
DATE_SUB( CURRENT_DATE , INTERVAL (if(WEEKDAY( CURRENT_DATE )=6,7,WEEKDAY( CURRENT_DATE )+8)) DAY)

What this combination does is if current_date weekday is 6 (sunday) deduct 7, otherwise deduct the weekday plus 8 from the current date
The next combination does the same, plus adds 7 days to that result.

and, for "the Sunday after that" using:
DATE_ADD(DATE_SUB( CURRENT_DATE , INTERVAL (if(WEEKDAY( CURRENT_DATE )=6,7,WEEKDAY( CURRENT_DATE )+8)) DAY), INTERVAL 7 DAY)

So, to automate your where clause you could use:
where B.CANCEL.DATE >= DATE_SUB( CURRENT_DATE , INTERVAL (if(WEEKDAY( CURRENT_DATE )=6,7,WEEKDAY( CURRENT_DATE )+8)) DAY)
  and B.CANCEL.DATE <  DATE_ADD(DATE_SUB( CURRENT_DATE , INTERVAL (if(WEEKDAY( CURRENT_DATE )=6,7,WEEKDAY( CURRENT_DATE )+8)) DAY), INTERVAL 7 DAY)

Open in new window

Here's some further information to see it working:
**MySQL 5.6.6 m9 Schema Setup**:

    CREATE TABLE tbl
    	(`dt` datetime)
    ;
    	
    INSERT INTO tbl
    	(`dt`)
    VALUES
    	('2014-07-13 00:00:00'),
    	('2014-07-14 00:00:00'),
    	('2014-07-15 00:00:00'),
    	('2014-07-16 00:00:00'),
    	('2014-07-17 00:00:00'),
    	('2014-07-18 00:00:00'),
    	('2014-07-19 00:00:00'),
    	('2014-07-20 00:00:00'),
    	('2014-07-21 00:00:00'),
    	('2014-07-22 00:00:00'),
    	('2014-07-23 00:00:00'),
    	('2014-07-24 00:00:00'),
    	('2014-07-25 00:00:00'),
    	('2014-07-26 00:00:00'),
    	('2014-07-27 00:00:00'),
    	('2014-07-28 00:00:00'),
    	('2014-07-29 00:00:00'), 
    	('2014-07-30 00:00:00'),
    	('2014-07-31 00:00:00'),
    	('2014-08-01 00:00:00'),
    	('2014-08-02 00:00:00'),
    	('2014-08-03 00:00:00'),
    	('2014-08-04 00:00:00')
    ;

**Query 1**:

    select
          date_format(dt ,'%a, %d/%M/%Y') as dt
        , if(WEEKDAY(dt)=6,7,WEEKDAY(dt)+8) as deduct_days
        , date_format(DATE_SUB(dt, INTERVAL (if(WEEKDAY(dt)=6,7,WEEKDAY(dt)+8)) DAY) ,'%a, %d/%M/%Y')                           as prev_week_sun
        , date_format(DATE_ADD(DATE_SUB(dt, INTERVAL (if(WEEKDAY(dt)=6,7,WEEKDAY(dt)+8)) DAY), INTERVAL 7 DAY) ,'%a, %d/%M/%Y') as sun_after_that
    from tbl
    
    

**[Results][2]**:
    
    |                  DT | DEDUCT_DAYS |     PREV_WEEK_SUN |      SUN_AFTER_THAT |
    |---------------------|-------------|-------------------|---------------------|
    |   Sun, 13/July/2014 |           7 | Sun, 06/July/2014 |   Sun, 13/July/2014 |
    |   Mon, 14/July/2014 |           8 | Sun, 06/July/2014 |   Sun, 13/July/2014 |
    |   Tue, 15/July/2014 |           9 | Sun, 06/July/2014 |   Sun, 13/July/2014 |
    |   Wed, 16/July/2014 |          10 | Sun, 06/July/2014 |   Sun, 13/July/2014 |
    |   Thu, 17/July/2014 |          11 | Sun, 06/July/2014 |   Sun, 13/July/2014 |
    |   Fri, 18/July/2014 |          12 | Sun, 06/July/2014 |   Sun, 13/July/2014 |
    |   Sat, 19/July/2014 |          13 | Sun, 06/July/2014 |   Sun, 13/July/2014 |
    |   Sun, 20/July/2014 |           7 | Sun, 13/July/2014 |   Sun, 20/July/2014 |
    |   Mon, 21/July/2014 |           8 | Sun, 13/July/2014 |   Sun, 20/July/2014 |
    |   Tue, 22/July/2014 |           9 | Sun, 13/July/2014 |   Sun, 20/July/2014 |
    |   Wed, 23/July/2014 |          10 | Sun, 13/July/2014 |   Sun, 20/July/2014 |
    |   Thu, 24/July/2014 |          11 | Sun, 13/July/2014 |   Sun, 20/July/2014 |
    |   Fri, 25/July/2014 |          12 | Sun, 13/July/2014 |   Sun, 20/July/2014 |
    |   Sat, 26/July/2014 |          13 | Sun, 13/July/2014 |   Sun, 20/July/2014 |
    |   Sun, 27/July/2014 |           7 | Sun, 20/July/2014 |   Sun, 27/July/2014 |
    |   Mon, 28/July/2014 |           8 | Sun, 20/July/2014 |   Sun, 27/July/2014 |
    |   Tue, 29/July/2014 |           9 | Sun, 20/July/2014 |   Sun, 27/July/2014 |
    |   Wed, 30/July/2014 |          10 | Sun, 20/July/2014 |   Sun, 27/July/2014 |
    |   Thu, 31/July/2014 |          11 | Sun, 20/July/2014 |   Sun, 27/July/2014 |
    | Fri, 01/August/2014 |          12 | Sun, 20/July/2014 |   Sun, 27/July/2014 |
    | Sat, 02/August/2014 |          13 | Sun, 20/July/2014 |   Sun, 27/July/2014 |
    | Sun, 03/August/2014 |           7 | Sun, 27/July/2014 | Sun, 03/August/2014 |
    | Mon, 04/August/2014 |           8 | Sun, 27/July/2014 | Sun, 03/August/2014 |



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

Open in new window

0
 
5teveoCommented:
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
0
 
PortletPaulfreelancerCommented:
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

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

No suitable answers
0
 
PortletPaulfreelancerCommented:
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.
0
 
garyrobbinsAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.