Travailler avec dates, en paramètre ou en affichage, est toujours quelque peu compliqué. En effet, il faut toujours veiller à la culture utilisée pour ne pas se retrouver avec les jours en mois et les mois en jours.
On a le même problème avec les type numériques ou monétaires.

Tout cela dépend de la configuration du serveur, de l’instance et de la session…

Pour les paramètres, il est fortement recommandé d’utiliser systématiquement des paramètres typés et la fonction CONVERT.

Pour l’affichage, la fonction CONVERT est bien utile mais n’apporte pas la souplesse que l’on trouve par exemple dans le développement .NET.

2 fonctions arrivent avec Denali :

• PARSE() : permet de convertir selon une culture donnée
• FORMAT() : permet de formater un valeur selon un format défini

Cela permet un écriture se rapprochant de celle que l’on trouve dans des applications .NET (plus de cohérence pour les développeurs) mais permet aussi des conversions, certes communes mais compliquées en SQL :

• les décimaux avec une virgule, auparavant il fallait faire un REPLACE pour mettre un point comme séparateur
• les formats précis (ou exotiques) de date comme yyyy.MM.dd

Quelques exemples pour illustrer :

--pour la date du 9 août

Select FORMAT(getdate(), 'd', 'fr-FR')
--> donne 09/08/2011
Select FORMAT(getdate(), 'd', 'en-US')
--> donne 8/9/2011
Select FORMAT(getdate(), 'g', 'fr-FR')
--> donne 09/08/2011 23:40
Select FORMAT(getdate(), 'dd----MM')
--> donne 09----08

Select FORMAT(123.34, 'C0', 'fr-FR') as [Currency]
--> donne 123 €

Select PARSE('12,6' as decimal(9,4) USING 'fr-FR')
--> 12.6000

On notera aussi les fonctions TRY_PARSE() et TRY_CONVERT() qui renvoient NULL en cas d’échec de conversion.

Select TRY_CONVERT(decimal, 'not a numeric')
--> donne NULL

Pour d’autres exemples, je vous renvoie au blog d’Aaron Bertrand (en anglais) :

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/08/09/sql-server-v-next-denali-ctp3-t-sql-enhancements-format.aspx