Don't know why but while running the command
select CONVERT(datetime, getdate(),101)
if @CodeFilter2 is not null
'Oct 27 2016 3:55PM'
You're converting a
datetime to a
datetime? Are you expecting that the format used in
convert sticks around? It's just ignored, since it has no meaning for the conversion you're doing. If you want to convert a datetime to a varchar, you need to use something like
convert(varchar(max), getDate(), 101), which will give you the correct output
10/27/2016 - I have no idea why you'd expect either of your samples to be correct; they simply happen to work that way because the default conversion (based on locale and other context, which is very variable) happens to be your desired result (in the first case).
If you need to rely on specific formatting you must use explicit formatting. Or let the application handle it instead of the DB server. The proper format for the ODBC cannonical form (which seems to be the one you desire) is 121. Make sure you're converting to