SQL Server Month, Year and Between in SQL together

qhhrdooz 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(119)

I have a column IH_Date with data type Date , which stores full date information including day. User will enter only from & to (from month, from year, to month, to year) . The query should return the respective results.

I write this query with some online help it works fine but it is not an efficient & it is slow sometime. Does anyone have better solution.

Help will be appreciated.

SELECT *
FROM tbl_item_history IH
WHERE YEAR(IH.IH_Date) * 100 + MONTH(IH.IH_Date)
BETWEEN 2022 * 100 + 03
    AND 2023 * 100 + 02
ORDER BY IH.IH_DATE ASC

sql-server

来源：https://stackoverflow.com/questions/76624829/month-year-and-between-in-sql-together

1条答案

按热度按时间

n3ipq98p1#

Avoid converting every row of data into year and month values. Instead of converting the data to suit your parameter types, convert the parameters values into dates, and I suggest DATEFROMPARTS for this. This way an index on IH_Date can be leveraged by your query.

SELECT *
FROM tbl_item_history IH
WHERE IH.IH_Date >= DATEFROMPARTS(@FromYear,@FromMonth,1)
AND IH.IH_Date < DATEFROMPARTS(@ToYear,@ToMonth,1)
ORDER BY IH.IH_DATE ASC

Note you may need to add 1 month to the latter date so that the "To Month" is "inclusive". i.e.

SELECT *
FROM tbl_item_history IH
WHERE IH.IH_Date >= DATEFROMPARTS(@FromYear,@FromMonth,1)
AND IH.IH_Date < DATEADD(month,1,DATEFROMPARTS(@ToYear,@ToMonth,1))
ORDER BY IH.IH_DATE ASC

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Month, Year and Between in SQL together

1条答案

相关问题

热门标签

最新问答