Solved

Create date ranges in SQL queries??

Posted on 2009-07-03
24
250 Views
Last Modified: 2012-05-07
SQL experts:
I have a script that allows users to select a date range of :
Today
Yesterday
This Week
This month
Last Month
Last Year

I need to know how to write the query to create a start and end date for each selection above.
Is it possible to do in a query or would you need to do it in say Coldfusion first then use a variable??

I can accept a coldfusion answer or straight sql.

God Bless ALL..

<cfquery datasource = "#mydsn#" name = "myQ">

Select * from mytable

where date between ........  and .......

</cfquery>

Open in new window

0
Comment
Question by:LeadCo
  • 9
  • 7
  • 6
  • +1
24 Comments
 
LVL 5

Expert Comment

by:rizwanidrees
ID: 24776160
Option - A, Create a store procedure as follow

CREATE PROCEDURE ProcedureName
      @p1 int
AS
BEGIN
      SET NOCOUNT ON;
      if @p1 = 0
            -- Today
         SELECT * FROM MyTable WHERE DATE=GETDATE()
      ELSE if @p1 = 1
            -- Yesterday
         SELECT * FROM MyTable WHERE DATE=GETDATE()-1
      ELSE IF @p1 = 2
            -- This Week
         SELECT * FROM MyTable WHERE DATE BETWEEN GETDATE()-(DATEPART(dw, GETDATE())-1) AND  GETDATE()+(7-DATEPART(dw, GETDATE()))
      ELSE IF @p1 = 3
            -- Last Month
         SELECT * FROM MyTable WHERE MONTH(DATE)=MONTH(DATEADD(m, -1, GETDATE()))
      ELSE IF @p1 = 4
            -- Last Year
         SELECT * FROM MyTable WHERE Year(DATE)=Year(DATEADD(y, -1, GETDATE()))
      
END

Option - B, Don't create store procedure, use coldfusion to decide which query to run
0
 

Author Comment

by:LeadCo
ID: 24776182
Great! The stored procedure should be fine.

I don't see  :
Thismonth in SP, can you show me how to do thismonth and LastWeek as well (IF POSSIBLE)?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24776200
have a look.



CREATE PROCEDURE ProcedureName 

      @p1 int

AS

BEGIN

      SET NOCOUNT ON;

      if @p1 = 0 

            -- Today

         SELECT * FROM MyTable WHERE DATE=GETDATE()

      ELSE if @p1 = 1 

            -- Yesterday

         SELECT * FROM MyTable WHERE DATE=GETDATE()-1

      ELSE IF @p1 = 2

            -- This Week

         SELECT * FROM MyTable WHERE DATE BETWEEN GETDATE()-(DATEPART(dw, GETDATE())-1) AND  GETDATE()+(7-DATEPART(dw, GETDATE()))

      ELSE IF @p1 = 3

            -- Last Month

         SELECT * FROM MyTable WHERE MONTH(DATE)=MONTH(DATEADD(m, -1, GETDATE()))

      ELSE IF @p1 = 4

            -- Last Year

         SELECT * FROM MyTable WHERE Year(DATE)=Year(DATEADD(y, -1, GETDATE()))

      ELSE IF @p1 = 5

            -- This Month

         SELECT * FROM MyTable WHERE MONTH(DATE)=MONTH(GETDATE())

      ELSE IF @p1 = 6

            -- This last week

         SELECT * FROM MyTable WHERE Date between GETDATE()-7 and GETDATE()

      

END

Open in new window

0
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 100 total points
ID: 24776201
you could do just the same with a function
0: today
1: yesterday
2: begin of this week
3: end of this week
4: ...


create function selectdate(@p1 int) returns date
as
begin
  declare @x DATE
   
  select @x = case :p1
    when 0 then getdate()
    when 1 then getdate() -1
    when 2 then getdate()-datepart(dw,getdate())-1
    when 3 then getdate()-datepart(dw,getdate())+7
    -- etc
    end

  return (@x)
end

then you can use this in a query

select * from table where date between selectdate(2) and selectdate(3)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24776202
or may be like this:



CREATE PROCEDURE ProcedureName 

      @p1 int

AS

BEGIN

      SET NOCOUNT ON;

      if @p1 = 0 

            -- Today

         SELECT * FROM MyTable WHERE DATE=GETDATE()

      ELSE if @p1 = 1 

            -- Yesterday

         SELECT * FROM MyTable WHERE DATE=GETDATE()-1

      ELSE IF @p1 = 2

            -- This Week

         SELECT * FROM MyTable WHERE DATE BETWEEN GETDATE()-(DATEPART(dw, GETDATE())-1) AND  GETDATE()+(7-DATEPART(dw, GETDATE()))

      ELSE IF @p1 = 3

            -- Last Month

         SELECT * FROM MyTable WHERE MONTH(DATE)=MONTH(DATEADD(m, -1, GETDATE()))

      ELSE IF @p1 = 4

            -- Last Year

         SELECT * FROM MyTable WHERE Year(DATE)=Year(DATEADD(y, -1, GETDATE()))

      ELSE IF @p1 = 5

            -- This Month

         SELECT * FROM MyTable WHERE MONTH(DATE)=MONTH(GETDATE())

      ELSE IF @p1 = 6

            -- This last week

         SELECT * FROM MyTable WHERE Date between GETDATE()-14 and GETDATE()-7

      

END

Open in new window

0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 24776204
advantage with function is, you can use it with any query or table
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 200 total points
ID: 24776209
moreover, you have to consider year in month checking otherwise if you will check for last month which is JUNE, it will return records for JUNE 2008/9/7 etc.



CREATE PROCEDURE ProcedureName 

      @p1 int

AS

BEGIN

      SET NOCOUNT ON;

      if @p1 = 0 

            -- Today

         SELECT * FROM MyTable WHERE DATE=GETDATE()

      ELSE if @p1 = 1 

            -- Yesterday

         SELECT * FROM MyTable WHERE DATE=GETDATE()-1

      ELSE IF @p1 = 2

            -- This Week

         SELECT * FROM MyTable WHERE DATE BETWEEN GETDATE() and GETDATE()-7

      ELSE IF @p1 = 3

            -- Last Month

         SELECT * FROM MyTable WHERE MONTH(DATE)=MONTH(DATEADD(m, -1, GETDATE())) and YEAR(date)=year(GETDATE())

      ELSE IF @p1 = 4

            -- Last Year

         SELECT * FROM MyTable WHERE Year(DATE)=Year(DATEADD(y, -1, GETDATE()))

      ELSE IF @p1 = 5

            -- This Month

         SELECT * FROM MyTable WHERE MONTH(DATE)=MONTH(GETDATE())  and YEAR(date)=year(GETDATE()) 

      ELSE IF @p1 = 6

            -- This last week

         SELECT * FROM MyTable WHERE Date between GETDATE()-14 and GETDATE()-7

      

END

Open in new window

0
 
LVL 5

Assisted Solution

by:rizwanidrees
rizwanidrees earned 200 total points
ID: 24776213
           -- Last Week
       
 SELECT * FROM MyTable WHERE DATE BETWEEN (GETDATE()-(DATEPART(dw, GETDATE())-1)) -7 AND  DATE BETWEEN (GETDATE()-(DATEPART(dw, GETDATE())-1)) -1

-- this Month
         SELECT * FROM MyTable WHERE MONTH(DATE)=MONTH(GETDATE())
-- This Year
         SELECT * FROM MyTable WHERE Year(DATE)=Year(GETDATE())
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24776215
>>advantage with function is, you can use it with any query or table<<

if this is for specific purpose, I recommend to go for SP only, function can be used with any query but what about performance?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24776216
@Rizwanidrees,

>>-- this Month
         SELECT * FROM MyTable WHERE MONTH(DATE)=MONTH(GETDATE())
-- This Year
         SELECT * FROM MyTable WHERE Year(DATE)=Year(GETDATE())<<

what about year while checking for month???
0
 
LVL 5

Expert Comment

by:rizwanidrees
ID: 24776220
You must have to add
and YEAR(date)=year(GETDATE())

for this month and last month, i forgot to type
0
 

Author Comment

by:LeadCo
ID: 24776221
Thanks all?

RiteshShaw: With this script would the "last week" and "this week" consider the start of the week?
Like if today is wednesday and they select last week they will  need to be returned Monday to Sunday or even Sunday to Saturday of last week would work.

Does the Last week and this week takwe that in consideration?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 5

Expert Comment

by:rizwanidrees
ID: 24776228
You are right RiteshShah, i forgot that
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24776229
no, in my approach it simply deduct 7 days from current day. for the approach you want, go for DatePart function like this:

DATE BETWEEN GETDATE()-(DATEPART(dw, GETDATE())-1) AND  GETDATE()+(7-DATEPART(dw, GETDATE()))

as Rizwanidrees has already explained.
0
 
LVL 5

Expert Comment

by:rizwanidrees
ID: 24776237
LeadCo: as per my understanding RetishShah script will give you result of last 7 days not for week. if you want recorder for monday to friday than try my query. What you suggest RetishShah?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24776239
>>What you suggest RetishShah?<<

I have already addressed that, you were right in that area, as I misunderstood the question bit so deducted simply 7 days.
0
 

Author Comment

by:LeadCo
ID: 24776252
Ok I am using rizwanidrees last week query ; So how about "this week" query, with that approach, affraid i am novice in SQL...
0
 

Author Comment

by:LeadCo
ID: 24776254
This week starting on mon or sun
0
 

Author Comment

by:LeadCo
ID: 24776274
rizwanidrees:
DATE BETWEEN GETDATE()-(DATEPART(dw, GETDATE())-1) AND  GETDATE()+(7-DATEPART(dw, GETDATE()))

This returns The current week starting on 06/29 Monday of this week. (Great for "This Week")

How would you go back one more week? to make it Last weeks numbers?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24776283
if you will run below query, you will know starting of last week and ending of the same.

select GETDATE()-(DATEPART(dw, GETDATE())-1),  GETDATE()+(7-DATEPART(dw, GETDATE()))
0
 
LVL 5

Expert Comment

by:rizwanidrees
ID: 24776284
DATE BETWEEN GETDATE()-(DATEPART(dw, GETDATE())-1)-7 AND  GETDATE()+(7-DATEPART(dw, GETDATE()))-14
0
 

Author Comment

by:LeadCo
ID: 24776428
Ok Here is what Got me "Last Week":

 DATE BETWEEN GETDATE()-(DATEPART(dw, GETDATE())-1)-7 AND  GETDATE()+(7-DATEPART(dw, GETDATE()))-7

0
 

Author Closing Comment

by:LeadCo
ID: 31599728
Thanks!
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24776500
glad to help!!!

Ritesh Shah

www.SQLHub.com
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

864 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now