問題描述
我需要編寫一個存儲過程來允許某人搜索數(shù)據(jù)庫.但是，我得到的只是月份和年份的整數(shù).數(shù)據(jù)庫有月份和年份字段.但我不知道如何設(shè)置比較.
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.
我需要在數(shù)據(jù)庫中搜索日期(由月份和年份字段指定)在這兩個日期之間的記錄.
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 輸入，我如何找到落在這些日期之間的所有記錄?每條記錄只有代表年和月的整數(shù).
Given two Date inputs, how do I find all records that fall between those dates? Each record only has integers representing year and month.
推薦答案
假設(shè)您在 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 到字符串轉(zhuǎn)換的左填充 - 受啟發(fā)的
Replace(Str(MyTable.Month, 2), ' ' , '0') - TSQL 之間
- Simple Left-Padding for Int to String Conversion - Inspired
Replace(Str(MyTable.Month, 2), ' ' , '0') - TSQL Between
這篇關(guān)于如何僅與月份和年份進(jìn)行比較，而不是完整日期?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！