请教一个时间段查询语句

[linlingwei]

declare
@date1 datetime ='2022-11-29 00:00:00'
declare
@date2  datetime ='2022-11-29 23:59:59'

exec('insert into ceshi_biao (dj_num,sh_date)'+
 'select dj_num,sh_date from dj_total where '+
'(sh_date>='+''''+@date1+''''+'and sh_date<='+''''+@date2+''''+')   and flag1='+'1113'+' and gys_kh_name='+''''+'张三'+'''' )

 

测试上面语句，结果 2022-11-29 23:59:40 的记录却查不到，

修改成 @date2  datetime ='2022-11-30 00:00:00' 就能查询到了。

请教这是什么原因？

[linlingwei]

@morliz子轩 感谢，问题解决。

[morliz子轩]

你写的语句，有问题。
如果用datetime类型，则需要把datetime转nvarchar传给动态语句，如果直接传，实际上这个datetime变量值会丢失，或者类型转换失败。

改成这样就好了：

declare @date1 datetime ='2022-11-29 00:00:00'
declare @date2 datetime ='2022-11-29 23:59:59'

-- select @date1,@date2

declare @sqlStr nvarchar(max)
set @sqlStr ='insert into ceshi_biao(dj_num, sh_date)'
set @sqlStr +=' select dj_num, sh_date from dj_total'
set @sqlStr +=' where (sh_date >=''' +convert(nvarchar(max),@date1,20)+ ''''
set @sqlStr +=' and sh_date <=' +'''' +convert(nvarchar(max),@date2,20)+ '''' +') and flag1 = 1113'
set @sqlStr +=' and gys_kh_name =''张三'''

select @sqlStr
--如果打印生成的语句没有问题，就直接可以exec执行了
exec @sqlStr

[linlingwei]

@吉普赛的歌 感谢，问题解决。

[吉普赛的歌]

完全按你原来的思路，应该这样改写：

declare @date1 datetime ='2022-11-29 00:00:00'
declare @date2  datetime ='2022-11-29 23:59:59'
DECLARE @sql NVARCHAR(MAX)

SET @sql='insert into ceshi_biao (dj_num,sh_date)'+
 'select dj_num,sh_date from dj_total where '+
'(sh_date>='+''''+CONVERT(CHAR(19),@date1,120)+''''+' and sh_date<='+''''+ CONVERT(CHAR(19),@date2,120)+''''+')   and flag1='+'1113'+' and gys_kh_name='+''''+'张三'+''''

PRINT @sql
/*
insert into ceshi_biao (dj_num,sh_date)
select dj_num,sh_date 
from dj_total 
where (sh_date>='2022-11-29 00:00:00'and sh_date<='2022-11-29 23:59:59') 
    and flag1=1113 
    and gys_kh_name='张三'
*/
EXEC (@sql);

[吉普赛的歌]

如果其它条件固定，只有@date1,@date2是传入的，则更简单，不需要动态SQL:

declare @date1 datetime ='2022-11-29 00:00:00'
declare @date2  datetime ='2022-11-29 23:59:59'

insert into ceshi_biao (dj_num,sh_date)
select dj_num,sh_date 
from dj_total 
where (sh_date>=@date1 and sh_date<=@date2) 
    and flag1=1113 
    and gys_kh_name='张三'