Solved

SQL date range

Posted on 2014-07-22
6
200 Views
Last Modified: 2014-07-31
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
0
Comment
Question by:garyrobbins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:5teveo
ID: 40211875
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40213929
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40214041
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:garyrobbins
ID: 40230225
I've requested that this question be deleted for the following reason:

No suitable answers
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40230227
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
 

Author Closing Comment

by:garyrobbins
ID: 40231830
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question