根据年份和月份选择行 [英] select rows based on year and month

问题描述

我有一张表

register(Empname varchar(),punchadte varchar(40)).

我创建了一个SP，并且分别经过了年月.
我想根据传递的年份和月份来选择行，并读取每一行.

I have created a SP and I pass year and month seperately.
I want to select rows based on year and month which are passed and read every rows.

推荐答案

我认为DATEPART 函数可用于此目的，如图所示下方:

I think the DATEPART function can be used for this purpose as shown below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetRowsByYearAndMonth 
	@Year int , 
	@Month int 
AS
BEGIN
	SELECT * 
	FROM register
	WHERE DATEPART(YEAR,punchdate)= @Year AND DATEPART(MONTH,punchdate)=@Month
END
GO

--Pass the year and month like
EXEC GetRowsByYearAndMonth 1998, 5

ALTER PROCEDURE dbo.AttendenceReportSP

	(
	@Option int,
	@compname varchar(30),
	@deptname varchar(30),
	@year varchar(4),
	@month varchar(10)
	)
	
AS
begin



    declare @datetimestring varchar(30),@loccode varchar(30),@DateTime datetime,@years varchar(4),@months varchar(10),@ff varchar(12),@y int,@m int,@empcode int --declaration section
    
    if @option=0
       begin
       select @y=convert(int,@year)
       if @month=convert(varchar,'January')
       select @m=convert(int,'01')
       else if @month=convert(varchar,'February')
       select @m=convert(int,'02')
          else if @month=convert(varchar,'March')
       select @m=convert(int,'03')
          else if @month=convert(varchar,'April')
       select @m=convert(int,'04')
          else if @month=convert(varchar,'May')
       select @m=convert(int,'05')
          else if @month=convert(varchar,'June')
       select @m=convert(int,'06')
          else if @month=convert(varchar,'July')
       select @m=convert(int,'07')
          else if @month=convert(varchar,'August')
       select @m=convert(int,'08')
          else if @month=convert(varchar,'September')
       select @m=convert(int,'09')
          else if @month=convert(varchar,'October')
       select @m=convert(int,'10')
          else if @month=convert(varchar,'November')
       select @m=convert(int,'11')
          else if @month=convert(varchar,'December')
       select @m=convert(int,'12')
       
       
     select @ff=PunchDate from register
        select @DateTime=convert(datetime,@ff,101)
        select @years=DATEPART(year,convert(datetime,@DateTime))
        select @months=DATEPART(month,convert(datetime,@DateTime))
       select * from registerwhere @y=@years and @m=@months
       end
RETURN
end

SELECT *
    FROM register
    WHERE YEAR(punchdate)= @Year AND MONTH(punchdate)=@Month

这篇关于根据年份和月份选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！