如何仅与月份和年份进行比较，而不是完整日期?

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我需要编写一个存储过程来允许某人搜索数据库.但是，我得到的只是月份和年份的整数.数据库有月份和年份字段.但我不知道如何设置比较.

I need to write a stored procedure to allow someone to search a db. However, all I get are ints for month and year. And the db has month and year fields. But I can't figure out how to set up the comparison.

例如:我得到 2008 年 3 月和 2010 年 6 月.

Ex: I get March 2008 and June 2010.

我需要在数据库中搜索日期(由月份和年份字段指定)在这两个日期之间的记录.

I need to searhc the database for records where the date, as specified by the month and year fields, are between thoese two dates.

编辑

给定两个 Date 输入，我如何找到落在这些日期之间的所有记录?每条记录只有代表年和月的整数.

Given two Date inputs, how do I find all records that fall between those dates? Each record only has integers representing year and month.

推荐答案

假设您在 SQL Server 中提供了名为 @StartDate 和 @EndDate 的日期变量:

Assuming you are provided Date variables called @StartDate and @EndDate in SQL Server:

SELECT
  *
FROM
  MyTable
WHERE  
  -- yields "200901 between 200801 and 201104" on inputs 01-01-2008, 04-01-2011
  Convert(VarChar(10), MyTable.Year) + Replace(Str(MyTable.Month, 2), ' ', '0')
   BETWEEN
  Convert(VarChar(10), YEAR(@StartDate)) + Replace(Str(MONTH(@StartDate), 2), ' ', '0')
   AND
  Convert(VarChar(10), YEAR(@EndDate)) + Replace(Str(MONTH(@EndDate), 2), ' ', '0')

参考资料

  • 简单用于 Int 到字符串转换的左填充 - 受启发的 Replace(Str(MyTable.Month, 2), ' ' , '0')
  • TSQL 之间
  • Simple Left-Padding for Int to String Conversion - Inspired Replace(Str(MyTable.Month, 2), ' ' , '0')
  • TSQL Between

相关文章