sql - different results when using query with variables and without variables -
i have query filter records in specific datetime range. testing purpose create query variables , not returning expected result.
here query:
declare @vtimefrom datetime = '2015-04-22 20:00:00.000' declare @vtimeto datetime = '2015-04-23 08:00:00.000' declare @ptime datetime = '2015-04-22 21:00:00.000' select @ptime convert(varchar(5),@ptime,108) between convert(varchar(5),@vtimefrom,108) , convert(varchar(5),@vtimeto,108)
it outputs:
no record found
the above query returns nothing.
but consider query :
declare @vtimefrom datetime = '2015-04-22 20:00:00.000' declare @vtimeto datetime = '2015-04-23 08:00:00.000' declare @ptime datetime = '2015-04-22 21:00:00.000' select @ptime convert(varchar(5),'2015-04-22 21:00:00.000',108) between convert(varchar(5),'2015-04-22 20:00:00.000',108) , convert(varchar(5),'2015-04-23 08:00:00.000',108)
it outputs:
april, 22 2015 21:00:00
convert(varchar(5),'2015-04-22 21:00:00.000',108)
left('2015-04-22 21:00:00.000', 5)
. in first case you're checking time , in second case you're checking strings.
declare @vtimefrom datetime = '2015-04-22 20:00:00.000' declare @vtimeto datetime = '2015-04-23 08:00:00.000' declare @ptime datetime = '2015-04-22 21:00:00.000' select convert(varchar(5),@ptime,108), convert(varchar(5),@vtimefrom,108), convert(varchar(5),@vtimeto,108), convert(varchar(5),'2015-04-22 21:00:00.000',108), convert(varchar(5),'2015-04-22 20:00:00.000',108), convert(varchar(5),'2015-04-23 08:00:00.000',108) ------------------------------------------------------ 21:00 20:00 08:00 2015- 2015- 2015-
Comments
Post a Comment