CODEWITHSUNDEEP
sql-server
Ryan Abarquez
Ryan Abarquez

Reputation: 307

SQL: How to query all data within the month from the given date?

I have a table tbl1 of ID Numbers and their corresponding Membership Date.

I want to create a stored procedure which has a @member_date parameter provided. I want the procedure to get the month and year of @member_date to get all the records covered by the month and date

ID_Number   Member_Date
10001        12/1/2015
10002        12/15/2015
10003        10/9/2015
10004        12/13/2014

Something like below:

@member_date = '12/25/2015'
Select * from tbl1 where month(Member_Date)=12 and year(Member_Date)=2015

Output:

ID_Number   Member_Date
10001        12/1/2015
10002        12/15/2015

Thank you for your help Greatly appreciated

Upvotes: 0

Views: 103

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44336

You should make your query Sargable for best performance

DECLARE @member_date date= '12/25/2015'

SELECT *
FROM
  tbl1
WHERE 
  Member_Date >= dateadd(month, datediff(month, 0, @member_date), 0)
  Member_Date < dateadd(month, datediff(month, -1, @member_date), 0)

Upvotes: 0

Pedram
Pedram

Reputation: 6508

Still I am not getting you proper, but I think you want something like below,

CREATE TABLE RYANTABLE (ID_NUMBER  INT, MEMBER_DATE DATE)
INSERT INTO RYANTABLE VALUES (10001,'12/1/2015')
INSERT INTO RYANTABLE VALUES (10002,'12/15/2015')
INSERT INTO RYANTABLE VALUES (10003,'10/9/2015')
INSERT INTO RYANTABLE VALUES (10004,'12/13/2014')

You need store procedure right like below ?

--EXEC RyanProcedure '12/25/2015' 
CREATE PROCEDURE RyanProcedure
    @member_date DATE
AS
BEGIN
    DECLARE @MONTH AS INT=MONTH(@member_date)
    DECLARE @Year AS INT=YEAR(@member_date)

    SELECT *
    FROM   RyanTable
    WHERE  (MONTH(Member_Date) = @MONTH  
           AND YEAR(Member_Date) = @Year)
           OR (Member_Date = @member_date)
END

Upvotes: 1

viduka
viduka

Reputation: 153

you can use 

DATE_FORMAT(<date>, <date_format>)

for example

SELECT * FROM TBL1 WHERE DATE_FORMAT(Member_Date, '%Y%m') = '201512'

%Y Year, numeric, four digits,

%m Month, numeric (00-12)

for date format, you can see in here

Upvotes: 0

Related Questions