如何查找结束日期小于月和年的记录

Question

我有一个包含id和joiningdate列的表employee，现在我想创建一个执行以下操作的查询:

1. 如果我传递月份和年份，那么所有连接日期小于或等于该日期(月份和年份)的记录都应该显示。

2. 如果month为空，则不应考虑月份，只应考虑年。

Answer 1

SELECT * FROM YourTable WHERE TheDate < 
   DATEADD(MONTH, CAST(COALESCE(@MONTH,1) AS int) - 1,CAST(CAST(@YEAR as varchar(4)) + '0101' AS DATETIME));

Answer 2

 DECLARE @tblTest AS TABLE 
 (
   Name  VARCHAR(50),
   JoiningDate Date
 )  
 INSERT INTO @tblTest
  Values ('Sandip','11-Feb-2016')
  ,('Jigar','20-Jun-2016')
  ,('Milan','05-Jan-2015')
  ,('Suresh','25-Apr-2015')

Declare @Month VARCHAR(20)='Feb',@Year VARCHAR(4)='2015'
Declare @LastDateOfMonth Date
IF ISNULL(@Month,'')=''
BEGIN
    SET @Month='DEC'
END
SET @LastDateOfMonth=DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, CAST('01-'+ @Month+'-'+@Year AS DATE)) + 1, 0))
SELECT
    *
FROM @tblTest
WHERE   
    JoiningDate<=@LastDateOfMonth

Answer 3

希望这对你有所帮助:

Declare @yourtable table (EmpName varchar(10), JoiningDate datetime) 
 INSERT INTO @yourtable
  Values ('A','10-Jan-2016')
  ,('B','20-Feb-2016')
  ,('C','05-Apr-2016')
  ,('D','31-Dec-2016')
Declare @month varchar(3) = ''
Declare @year varchar(4) = '2016'
select * 
from @yourtable
Where (isnull(@month,'') <> '' and 
(JoiningDate <= (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,cast('01-'+@month+'-'+@year as date))+1,0)))))
or (isnull(@month,'') = '' and (Year(JoiningDate) =  @year))