CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

different results when using query with variables and without variables

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

Upvotes: 3

Views: 99

Answers (3)

roman
roman

Reputation: 117345

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-

Upvotes: 2

flo
flo

Reputation: 10241

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.

Upvotes: 3

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Those formats are for date types, they don't work for strings. So they returning 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 fiddle: http://sqlfiddle.com/#!6/9eecb/4727

Upvotes: 2

Related Questions