Sorry, i had a very detailed solution, but first my Computer crashed, the i copy-pasted it over and this edit window doesnt Support STRG-Z, Argh.
Ok, to be briefly:
Your issue is because SqlServer interprets the specified strings in Statement
BETWEEN
in the format MM-DD-YYYY.
while you get expected result supplying 30-07-2012 this seems to be no Problem, while supplying 01-07-2012 keeps to be weird. that is, becaus in case of 01-07-2012, SqlServer can parse the MM- and DD-portions without complains, resulting in a dateime like 07-01-2012 when formatted as DD-MM-YYYY. Even if that is what makes problems, everything works as expected here. The actual Problem is the string 30-07-2012: SqlServer Ends up in a Situation like "hmmm, 30 is way to high to be the MM-Portion, so it must be the DD-Portion...therefore 07 must be the DD-Portion..." - resulting in a datetime that looks like 30-07-2012 when formatted with DD-MM-YYYY. Funny, that this was actually that what you expected, but achieved by accident.
another subtle advices:
1.)keep conversions in one Technology-Domain! either convert thos datetimes in SqlServer when pulling out data and passing in data or do it in .NET, never mix, as you will face exactly such a Problem like yours today.
2.) don't ever use string-concatination for building SQL-Statements, even in dirty little prototypes, as you will develop a bad habbit opening your application with possible SqlInjections and lose the comfort of automatic type-conversions (maybe you wouldn't have had this Problem when using
SqlParameters[
^]).
best regards!