SQL Server : Conserver la date ou l’heure d’un datetime (Comparaison des méthodes)
Les types dates de SQL Server, comportent toujours la date et l'heure alors que souvent nous ne souhaitons utiliser que l'un ou l'autre. Dans ce genre de situation nous « resetons » manuellement l'un ou l'autre partie qui ne intéresse pas.
Tout d'abord comment ne conserver que la partie date, cette liste et non limitative, et vous pourrez peut être trouver mieux en terme de perf.
SELECT CONVERT(datetime, CONVERT(char(10), X, 103), 103) FROM DateTable -- 18.5 sec
SELECT DATEADD(d, 0, FLOOR(CAST(X as float))) FROM DateTable -- 7.5 sec
SELECT CAST(FLOOR(CAST(X as float)) as datetime) FROM DateTable -- 6.3 sec
SELECT CAST(CAST(CAST(X as float) as int) as datetime) FROM DateTable -- 4.5 sec
-- Les conversions qui suivent ne fonctionnent pas à cause de l'arrondi
SELECT DATEADD(d, 0, CAST(X as int)) FROM DateTable -- 4.9 sec
SELECT CAST(CAST(X as int) as datetime) FROM DateTable -- 4.0 sec
X représente le champ date à convertir, DateTable contient environ 19 millions de dates (date et heure) pour effectuer les tests. Le temps indiqué derrière correspond au temps CPU approximatif consommé par la requête (sur ma machine).
La première méthode est la plus lente, elle repose sur CONVERT qui permet contrairement à CAST de passer le format souhaité de date. On a choisit le format 103 qui correspond à JJ/MM/AAAA c'est-à-dire le format utilisé en France. Ce format n'indique pas l'heure et qui permet en effectuant 2 CONVERT successif d'obtenir uniquement la date avec l'heure à minuit.
La seconde repose sur une conversion de date en float, un arrondit du float et une reconversion en datetime. Le gain est très net par rapports aux fonctions de date utilisé pour le premier exemple. Cette méthode je l'ai découverte grâce à Nix, ici.
Partons de cette méthode pour aller plus vite. Clairement l'arrondi du float est en trop, on peut réaliser la conversion directement en int. Quant au DATEADD il ne fait qu'une reconversion du int en datetime donc autant refaire un CAST en datetime à la place. Le problème comme l'a fait remarquer Nicolas en commentaire c'est que ces méthodes revoient un résultat faux à cause de l'arrondi produit par le CAST datetime vers int. Avec le DATEADD remplacé par un CAST on gagne une seconde de temps CPU. Et le FLOOR est remplacé par un CAST, le CAST d'un float vers int effectue une troncation des décimales, ce qui est l'effet souhaité chez nous.
Il n'y a pas de différence de performance entre l'utilisation du CONVERT ou du CAST, je préfère le CAST celui-ci étant un standard SQL. Le comportement de toutes les méthodes citées plus haut est identique pour un datetime et smalldatetime.
Maintenant pour conserver la composante heure d'un datetime.
SELECT CONVERT(datetime, CONVERT(char(10), X, 114), 114) FROM DateTable – 18.9 sec
SELECT CAST(CAST(X as binary(4)) as datetime) FROM DateTable – 5.7 sec
La première est la copie conforme de la première utilisée pour la partie date, elle d'ailleurs tout aussi rapide :o>
La seconde méthode convertit la date en binary(4) ce qui a pour effet de tronquer la partie date pour un datetime. Pour un smalldatetime il faudra convertir en binary(2) pour obtenir le même effet.
Si vous trouvez mieux et plus rapide postez un commentaire sur cette page. Merci :o)
Bon dev…
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
