关于sql语句日期的统计语句_sql统计没有任务的日期-CSDN博客

本文链接：https://blog.csdn.net/FormatRain/article/details/126050208

本文介绍了SQL查询中用于获取特定时间范围内的数据的方法，包括当天、本周、上月、本月、本季度和本年的查询。此外，还提供了检查SQL Server中表、字段、存储过程和触发器是否存在的实用SQL语句。最后，分享了如何复制表结构和进行多表更新的操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

–本天

SELECT *FROM dbo.TableName WHERE DATEDIFF(DAY,TimeField,getdate())= 0;

–本周

SELECT *FROM dbo.TableName WHERE DATEDIFF(WEEK,TimeField,getdate())= 0;

–上月

SELECT *FROM dbo.TableName WHERE DATEDIFF(WEEK,TimeField,getdate())= -1;

–本月

SELECT *FROM dbo.TableName WHERE DATEDIFF(MONTH,TimeField,getdate())= 0;

–本季度

SELECT *FROM dbo.TableName WHERE DATEPART(qq, TimeField) = DATEPART(qq, GETDATE()) and DATEPART(yy, TimeField) = DATEPART(yy, GETDATE());

–本年

SELECT *FROM dbo.TableName where DATEDIFF(YEAR, TimeField,getdate())= 0;

注：TableName为查询的表名，TimeFiled为查询的时间字段

二、SQL SERVER 判断表、字段、存储过程、触发器是否存在

–判断表是否存在

SELECT *FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('表名') AND OBJECTPROPERTY(object_id, N'IsUserTable') = 1

–或

SELECT *FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('表名') AND type='u'

–判断字段是否存在

SELECT *FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('字段名') AND Name = 'No'

–判断存储过程是否存在

SELECT *FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('存储过程名') AND

OBJECTPROPERTY(object_id, N'IsProcedure') = 1

–或

SELECT *FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('存储过程名') AND type='p'

–判断触发器是否存在

SELECT *FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('触发器名') AND OBJECTPROPERTY(object_id, N'IsTrigger') = 1

–或

SELECT *FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('触发器名') AND type='TR'

三、表复制语句

– 要求表2不存在，查询时自动创建

Select Field1,Field2 from Table1 Into Table2

–要求表2必须存在，可以插入常量

Insert into table2 (Field1,Field2) select Field1,Field2 from table1

四、多表更新

> UPDATE Table1 SET T1F1 = T2F1, T1F2 = T2F2, T1F3 = T2F3 FROM Table1,
> Table2 WHERE Table1.Key = Table2.Key 
> 如： update sms_maintainLog set
> WorkerID=a.LastWorkerID from sms_maintainLog  t inner join
> sms_DeviceRecord a on t.deviceno=a.deviceno where a.LastWorkerID is
> not null and t.WorkerID is null