Different results when using a query with and without variables
I have a query that filters records in a specific datetime range. For testing purposes, I am creating a query with variables and does not return the expected result.
Here is the request:
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
where Convert(Varchar(5),@pTime,108)
BETWEEN Convert(Varchar(5),@vTimeFrom,108) and Convert(Varchar(5),@vTimeTo,108)
It outputs:
No records found
The above query returns nothing.
But consider this request:
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
where Convert(Varchar(5),'2015-04-22 21:00:00.000',108)
between Convert(Varchar(5),'2015-04-22 20:00:00.000',108)
and Convert(Varchar(5),'2015-04-23 08:00:00.000',108)
It outputs:
April 22, 2015 21:00:00
source to share
Convert(Varchar(5),'2015-04-22 21:00:00.000',108)
really simple left('2015-04-22 21:00:00.000', 5)
. So in the first case, you check the time, and in the second case, you check the 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-
source to share
Select Convert(Varchar(5),'2015-04-22 21:00:00.000',108), Convert(Varchar(5),@pTime,108) , @pTime
gives you the answer:
2015- | 21:00 | 2015-04-22 21:00:00
The first direct formatting involves converting varchar and thus including the style attribute, while the second converter takes date and time.
To get an example without variables working, you can use
Convert(Varchar(5), (cast ('2015-04-22 21:00:00.000' as datetime)),108)
to make sure the conversion is converting from datetime.
source to share
These formats are date types; they do not work for strings. Therefore, they return different substrings.
Declare @vTimeFrom datetime = '2015-04-22 20:00:00.000'
Select Convert(Varchar(5),@vTimeFrom,108),
Convert(Varchar(5),'2015-04-22 20:00:00.000', 108)
Output:
20:00 2015-
Here is a fiddle: http://sqlfiddle.com/#!6/9eecb/4727
source to share