需要根据修改日期计算sql中的行数
本文关键字:sql 计算 日期 修改 | 更新日期: 2023-09-27 18:30:52
我想做的是计算更新 2 天的表的行数,这意味着假设我有一些这样的数据:
ID Name Department DateTime ModifyDate
---------------------------------------------------------------------------
1 XXXXX IT 2013-09-10 09:53:01.000 2013-09-10 09:55:01.000
2 YYYYY Tech 2013-09-09 10:00:00.000 2013-09-10 12:00:00.000
3 WWWW IT 2013-09-09 08:09:00.000 2013-09-10 09:09:00.000
现在有两行在 9 月 10 日修改
我想要一个查询,它将给我每天的行修改计数。
我想要的出来会是这样的
No. of column
2
试试这个
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("Select count(ID) from TableName where ModifyDate>=@Today and ModifyDate<@NextDay", connection))
{
command.Parameters.Add(new SqlParameter("Today", DateTime.Today ));
command.Parameters.Add(new SqlParameter("Nextday", DateTime.Today.AddDays(1) ));
int count = 0;
int.TryParse( command.ExecuteScalar().ToString() , out count);
}
}
select Distinct Count(ID) from Table where ModifyDate between '2013-09-10' and '2013-09-11'
SELECT COUNT(ID) FROM TableX WHERE ModifyDate BETWEEN @FromDate and @ToDate;
将开始日期和结束日期传递给相应的变量
你可能想使用类似的东西
SELECT count(ID)
FROM TableName
GROUP BY CAST(ModifyDate AS DATE)
SELECT CONVERT(DATE,ModifiedDate) AS DATE, count(ID) AS TotalUpdated
FROM TableName
GROUP BY CONVERT(DATE,ModifiedDate)
请尝试此查询。
select count(*) as [No. of column] from Table1
where CAST(ModifyDate as Date) = 'Sep 10, 2013'
如果要按当前日期进行筛选,请将"Sep 10, 2013"替换为 GetDate()。