How To Fetch Year And Month Record With Store Proc

shah Momin

4y1kC#

SELECT * FROM tblattendancelog WHERE MONTH(dutydatetime)
BETWEEN '01' and '01' AND YEAR(dutydatetime) BETWEEN 2009 and 2020

i Will Select " Month " and Year Get Data

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

shah Momin4y ago
I Will try Yealy - Month Daily example in image
Amit Mohanty4y ago
Try this
CREATE PROCEDURE GetAttendanceLog
(
    @FromMonth AS INT = 1,
    @ToMonth AS INT = 2,
    @FromYear AS INT = 2020,
    @ToYear AS INT = 2022
)
AS
BEGIN
    SELECT * FROM tblattendancelog WHERE MONTH(dutydatetime) BETWEEN @FromMonth AND @ToMonth
    AND YEAR(dutydatetime) BETWEEN @FromYear AND @ToYear
END

-- EXEC GetAttendanceLog 1, 2, 2021, 2022
Muhammad Imran Ansari4y ago
Try the following query:
-- In your storeprocedure take these parameters
DECLARE @MonthName AS NVARCHAR(20) = 'August'
DECLARE @StartYear AS INT = 2009
DECLARE @EndYear AS INT = 2020

-- If Parameter Month is already in number then take @Month as First Parameter and no need to do this
DECLARE @Month INT = MONTH(@MonthName + '1,1')

-- Here is your query
SELECT *
FROM tblattendancelog
WHERE MONTH(dutydatetime) BETWEEN @Month and @Month
AND YEAR(dutydatetime) BETWEEN @StartYear and @EndYear
Pushpendra Shukla4y ago
Use the year function to filter the year part from the dates.
SELECT table1.NAME,
       table1.licence,
       table2.date
FROM   table1
       INNER JOIN table2
               ON table2.table1_idtable1 = table1.idtable1
WHERE  Month (date) = Month(input_date)
       AND Year(date) = Year(input_date)
       AND result = 'passed'
A better approach with some date functions. It will use the index on the date column.
SELECT table1.NAME,
       table1.licence,
       table2.date
FROM   table1
       INNER JOIN table2
               ON table2.table1_idtable1 = table1.idtable1
WHERE  date >= cast(Date_format(input_date, '%Y-%m-01') as date)
       AND date < DATE_ADD(cast(Last_day(input_date) as date),INTERVAL 1 DAY)
       AND result = 'passed'