SQL Server：获取当前月份和前几个月(SQL Server: get current month and previous months)

SQL Server：获取当前月份和前几个月(SQL Server: get current month and previous months)

我有一个表dateE格式为nvarchar(20)并包含格式为yyyy-mm-dd的日期。 以下存储过程从2014年4月起使用dateE从此表中获取所有记录：

BEGIN SET NOCOUNT ON; SELECT cat, COUNT(*) AS groupCount FROM Log_PE WHERE dateE LIKE '2014-04%' GROUP BY cat ORDER BY groupCount desc, cat END

我怎样才能改变这一点，以便我不必对日期进行硬编码，而是始终使用当前年份和月份？

另外，我想使用相同的存储过程来获取前三个月（在本例中为2014年1月，2月，3月）的相同数据，以便最终我有一个像我的例子中的排名，但每个以上几个月。

这里的任何人都可以帮助我吗？

蒂姆，非常感谢。

I have a table with a column dateE that is formatted as nvarchar(20) and contains dates in the format yyyy-mm-dd. The below stored procedure fetches all records from this table with dateE from Apr 2014:

BEGIN SET NOCOUNT ON; SELECT cat, COUNT(*) AS groupCount FROM Log_PE WHERE dateE LIKE '2014-04%' GROUP BY cat ORDER BY groupCount desc, cat END

How can I change this so that I don't have to hard-code the date and instead it always uses the current year and month for this ?

In addition, I would like to use the same stored procedure to also fetch the same data from the previous three months (in this case Jan, Feb, March 2014) so that in the end I have a ranking like in my example but for each of the above months.

Can anyone here help me with this ?

Many thanks in advance, Tim.

最满意答案

而不是比较字符串，比较日期。 尝试这个：

SELECT cat, COUNT(*) AS groupCount FROM Log_PE WHERE CONVERT(DATE, dateE, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), GETDATE(), 112) + '01', 112) GROUP BY cat ORDER BY groupCount desc, cat

说明：

--Convert your string date column to a date type (yyyy-mm-dd --> 120) CONVERT(DATE, dateE, 120) --Convert the current date to yyyymmdd format (style 112) --and get the first 6 characters. ie; Year and month CONVERT(VARCHAR(6), GETDATE(), 112) --Add `'01'` to the `yyyymm` and convert back to a date type CONVERT(DATE, CONVERT(VARCHAR(6), GETDATE(), 112) + '01', 112)

Instead of comparing strings, compare dates. Try this:

SELECT cat, COUNT(*) AS groupCount FROM Log_PE WHERE CONVERT(DATE, dateE, 120) >= CONVERT(DATE, CONVERT(VARCHAR(6), GETDATE(), 112) + '01', 112) GROUP BY cat ORDER BY groupCount desc, cat

Explanation:

--Convert your string date column to a date type (yyyy-mm-dd --> 120) CONVERT(DATE, dateE, 120) --Convert the current date to yyyymmdd format (style 112) --and get the first 6 characters. ie; Year and month CONVERT(VARCHAR(6), GETDATE(), 112) --Add `'01'` to the `yyyymm` and convert back to a date type CONVERT(DATE, CONVERT(VARCHAR(6), GETDATE(), 112) + '01', 112)