Create date ranges in SQL queries??

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)?

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

advantage with function is, you can use it with any query or table

>>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?

@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???

You must have to add
and YEAR(date)=year(GETDATE())

for this month and last month, i forgot to type

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?

You are right RiteshShah, i forgot that

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.

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?

>>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.

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

This week starting on mon or sun

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?

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()))

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

Ok Here is what Got me "Last Week":

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

Thanks!