mssql sqlserver 对日期字段进行模糊查询的三种方法分享


摘要:
下文讲述对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]