Create date ranges in SQL queries??

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

LeadCoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rizwanidreesCommented:
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
LeadCoAuthor Commented:
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
RiteshShahCommented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Geert GOracle dbaCommented:
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
RiteshShahCommented:
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
Geert GOracle dbaCommented:
advantage with function is, you can use it with any query or table
0
RiteshShahCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rizwanidreesCommented:
           -- 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
RiteshShahCommented:
>>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
RiteshShahCommented:
@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
rizwanidreesCommented:
You must have to add
and YEAR(date)=year(GETDATE())

for this month and last month, i forgot to type
0
LeadCoAuthor Commented:
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
rizwanidreesCommented:
You are right RiteshShah, i forgot that
0
RiteshShahCommented:
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
rizwanidreesCommented:
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
RiteshShahCommented:
>>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
LeadCoAuthor Commented:
Ok I am using rizwanidrees last week query ; So how about "this week" query, with that approach, affraid i am novice in SQL...
0
LeadCoAuthor Commented:
This week starting on mon or sun
0
LeadCoAuthor Commented:
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
RiteshShahCommented:
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
rizwanidreesCommented:
DATE BETWEEN GETDATE()-(DATEPART(dw, GETDATE())-1)-7 AND  GETDATE()+(7-DATEPART(dw, GETDATE()))-14
0
LeadCoAuthor Commented:
Ok Here is what Got me "Last Week":

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

0
LeadCoAuthor Commented:
Thanks!
0
RiteshShahCommented:
glad to help!!!

Ritesh Shah

www.SQLHub.com
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.