Amazing SQL Server behavior - leads to different formats if the WHERE clause contains the expression 1 <> 2

Question

I have two almost identical SELECT statements. I run them on SQL Server 2012 with the Danish_Norwegian_CI_AS server setup and the Danish_Norwegian_CI_AS database sorting. The database runs at the compatibility level installed on SQL Server 2005 (90).

I run both queries on the same client using SQL Server 2012 Management Studio. The client is a laptop running Windows 8.1.

the cryptic part, although the statements are almost identical, the result set is different, as shown below (one returns a 24-hour time format, the other with AM / PM, which receives a truncated tpo P in this case). The only difference is the expression 'and 1 <> 2' in the WHERE clause. I searched up and down, searched google, as deep as possible, could not find an explanation. Tried COLLATE to force conversion, did not help. If I use 108 for formatting in a CONVERT call, the results will be the same. But without knowing the reasons why this does not work, I ate me alive.

Problem recreated on SqlFiddle, SQL Server 2008:

http://sqlfiddle.com/#!3/a97f8/1

Ask someone to explain this?

SQL DDL . script , .

sql 1 < > 2 :

Id          StartTime
----------- ---------
2           2:00P
2           2:14P

sql 1 < 2 :

Id          StartTime
----------- ---------
2           14:00
2           14:14



if NOT EXISTS (Select * from sysobjects where name = 'timeVarchar')
begin
    create table timeVarchar (
        Id int not null,
        timeTest datetime not null
    )
end

if not exists (select * from timeVarchar) 
begin
    -- delete from timeVarchar
    print 'inserting'
    insert into timeVarchar (Id, timeTest) values (1, '2014-04-09 11:37:00')
    insert into timeVarchar (Id, timeTest) values (2, '1901-01-01 14:00:00')
    insert into timeVarchar (Id, timeTest) values (3, '2014-04-05 15:00:00')
    insert into timeVarchar (Id, timeTest) values (2, '1901-01-01 14:14:14')
end

select
        Id,
        convert ( varchar(5), convert ( time, timeTest)) as 'StartTime'
from
     timeVarchar
where
     Id = 2



select
     Id, 
        convert ( varchar(5), convert ( time, timeTest)) as 'StartTime'
from
     timeVarchar
where
     Id = 2 and
     1                       <>    2

Answer 1

, ( , ), :

select Id,
       convert (varchar(5), convert (time, timeTest), 14) as "StartTime"
from timeVarchar
where Id = 2;

select Id, 
       convert (varchar(5), convert (time, timeTest), 14) as "StartTime"
from timeVarchar
where Id = 2 
and  1 <> 2; 

, .

2 (!) CONVERT_IMPLICIT . !

, , , CONVERT(x, y, 0). , 0 - (~ US) . 0 , , 4 ( ).

, , , , , , , -.

-, convert . .

: , , MSDN:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

SQL Server CAST CONVERT datetime2 121, , . , 0. , , .

, 0, 121. 110+ (.. SQL SERVER 2012+) - 121.

Answer 2

, SQL2012

http://sqlfiddle.com/#!6/a97f8/4

p.s url sqlfiddle SQL2008