摘要:
下文讲述对sqlserver 日期字段进行模糊查询的方法,如下所示
实验环境:sql server 2008 R2
例:
查询出数据表maomao365.com中writeDate 写入时间为2019-6-2日的所有数据
---创建数据表
create table [maomao365.com](
keyId int identity(1,1),info varchar(30),writeDate datetime)
go
---生成模拟数据
insert into [maomao365.com](info,writeDate)values
('sqlblog','2019-6-1'),('sqlServer','2019-6-2 10:23:00'),
(N'猫猫小屋','2019-6-10'),('sql爱好者','2019-6-2 1:00:00')
go
---查询数据
---方法1:将字段转换为指定格式的string类型,然后进行数据查询
select keyId,info,writeDate from [maomao365.com] where convert(varchar,writeDate,120) like '2019-06-02%'
---方法2:对datetime字段使用between范围函数
select keyId,info,writeDate from [maomao365.com]
where writeDate between '2019-06-02 0:00:00' and '2019-06-02 23:59:59'";
---方法3 使用datediff()函数对数据进行对比
select keyId,info,writeDate from [maomao365.com]
where datediff(day,writeDate,'2019-06-2')=0
go
truncate table [maomao365.com]
drop table [maomao365.com]