How To Fetch Year And Month Record With Store Proc

Question

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

shah Momin · Answer

I Will try Yealy - Month Daily example in image

Amit Mohanty · Answer

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 Ansari · Answer

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 Shukla · Answer

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 cast (Last_day(input_date) as date ),INTERVAL 1 DAY ) AND result = 'passed'