【Sql Server】时间转换和查询时间范围查询不正确的原因

作者：小5聊基础

简介：一只喜欢全栈方向的程序员，欢迎咨询，尽绵薄之力答疑解惑

编程原则：Write Less Do More

下面进行场景重现下

1、创建表

创建只有三个字段的表testTable，自增编号、全球唯一编号Guid、添加时间

create table testTableName
(
  id int identity(1,1) primary key,
  guidValue nvarchar(50),
  createTime datetime
)

2、模拟一个月数据

1）可以写一个sql的循环语句按天模拟添加一个月的数据

2）定义一个整型变量，从0开始，循环30次

3）时间格式化，可以看我写的这篇文章《Sql Server日期格式化大全》

4）除了看时间格式化，还需要对时间进行加减《sql server 查询七天内的数据之时间条件格式化》

5）前一天设置，dateadd(day,-1,getdate())

declare @dayCount int
set @dayCount=0
while @dayCount<=30 begin
  insert into testTableName(guidValue,createTime)
  values(newid(),dateadd(day,-@dayCount,getdate()))
  set @dayCount+=1
end

编辑

3、按时间查询

查询2022.12.15到2022.12.19号的记录，正确情况应该是返回5条记录

时间格式用了三个方式，小数点、斜杠、横杠，都可能正确查询出来

• 正确返回

select * from testTableName 
where 1=1 and 
--createTime>='2022.12.15 00:00:00' and createTime<='2022.12.19 23:59:59'
createTime>='2022/12/15 00:00:00' and createTime<='2022/12/19 23:59:59'
--createTime>='2022-12-15 00:00:00' and createTime<='2022-12-19 23:59:59'

编辑

• 错误查询方式 - 小数点格式

编辑

• 错误查询方式 - 斜杠格式

编辑

• 正确返回 - 横杠格式

编辑

select * from(
  select id,guidValue,convert(varchar(20),createTime,23) as createTime
  from testTableName
) a 
where 1=1 and 
--createTime>='2022.12.15 00:00:00' and createTime<='2022.12.19 23:59:59'
--createTime>='2022/12/15 00:00:00' and createTime<='2022/12/19 23:59:59'
createTime>='2022-12-15 00:00:00' and createTime<='2022-12-19 23:59:59'

• 作为本身查询没问题

编辑

4、总结

1）如果查询字段作为时间范围筛选，那么尽量不要把时间字段转为字符串格式

2）如果时间格式转为字符串，那么不要在子查询外对字符串的时间字段进行时间范围筛选

3）如果要作为时间范围筛选，那么使用yyyy-MM-dd格式进行筛选，否则查询无效