different results when using query with variables and without variables

Question

I have a query which filter records in a specific datetime range. For testing purpose i create a query with variables and it is not returning the expected result.

Here is the 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
where Convert(Varchar(5),@pTime,108) 
BETWEEN Convert(Varchar(5),@vTimeFrom,108) and Convert(Varchar(5),@vTimeTo,108)

It outputs:

No record found

The above query returns nothing.

But consider this 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
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

Answer 1

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 is assuming varchar convert and thus ingnoring the style attribute while the second convert is assuming datetime.

To get the example without variables working you can use

Convert(Varchar(5), (cast ('2015-04-22 21:00:00.000' as datetime)),108)

to make sure convert is converting from datetime.

Answer 2

Convert(Varchar(5),'2015-04-22 21:00:00.000',108) is actually just left('2015-04-22 21:00:00.000', 5). So in first case you're checking time and 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-