Comment supprimer la partie time d'une valeur datetime (SQL Server)?

SQL Server 2008 and up

dans SQL Server 2008 et plus, bien sûr le moyen le plus rapide est Convert(date, @date) . Cela peut être rejeté vers un datetime ou datetime2 si nécessaire.

Ce qui Est Vraiment le Meilleur Dans SQL Server 2005 et plus?

j'ai vu des revendications incohérentes sur ce qui est le plus rapide pour tronquer l'heure à partir d'une date dans le serveur SQL, et certaines personnes même dit ils ont fait des tests, mais mon expérience a été différente. Alors faisons des tests plus rigoureux et laissons tout le monde avoir le script pour que si je fais des erreurs, les gens puissent me corriger.

Les Conversions De Flotteurs Ne Sont Pas Exactes

tout d'abord , je m'abstiendrai de convertir datetime en float , parce qu'il ne convertit pas correctement. Vous pouvez vous en sortir en faisant la chose de temps-enlèvement avec précision, mais je pense que c'est un mauvaise idée de l'utiliser parce qu'il implicitement communique aux développeurs que c'est une opération sûre et il n'est pas . Regardez:

declare @d datetime;
set @d = '2010-09-12 00:00:00.003';
select Convert(datetime, Convert(float, @d));
-- result: 2010-09-12 00:00:00.000 -- oops

ce n'est pas quelque chose que nous devrions enseigner aux gens dans notre code ou dans nos exemples en ligne.

aussi, ce n'est même pas le chemin le plus rapide!

"1519330920 De" Validation Des Tests De Performance

si vous voulez effectuer certains teste toi-même pour voir comment les différentes méthodes s'empilent vraiment, alors tu auras besoin de ce script de configuration pour exécuter les tests plus bas:

create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED);
declare @d datetime;
set @d = DateDiff(Day, 0, GetDate());
insert AllDay select @d;
while @@ROWCOUNT != 0
   insert AllDay
   select * from (
      select Tm =
         DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm)
      from AllDay
   ) X
   where Tm < DateAdd(Day, 1, @d);
exec sp_spaceused AllDay;  -- 25,920,000 rows

veuillez noter que cela crée une table de 427,57 Mo dans votre base de données et prendra environ 15-30 minutes à exécuter. Si votre base de données est petite et définie à une croissance de 10%, cela prendra plus de temps que si vous dimensionnez suffisamment grand en premier.

maintenant pour le script de test de performance. Il est à noter que intentionnel de ne pas retourner les lignes au client car c'est fou cher sur 26 millions de lignes et cach erait les différences de performance entre les méthodes.

Résultats De La Performance

set statistics time on;
-- (All queries are the same on io: logical reads 54712)
GO
declare
    @dd date,
    @d datetime,
    @di int,
    @df float,
    @dv varchar(10);

-- Round trip back to datetime
select @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms,  elapsed time = 22301 ms.
select @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms.
select @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms.
select @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms.
select @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms.
select @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms,  elapsed = 108236 ms.
select @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms.

-- Only to another type but not back
select @dd = Tm from AllDay; -- CPU time = 19891 ms,  elapsed time = 20937 ms.
select @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms.
select @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms
select @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms.
select @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms.
select @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms,  elapsed = 67987 ms.
select @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms.
GO
set statistics time off;

Certains Randonnée Analyse

quelques notes à ce sujet. Tout d'abord, si vous effectuez juste un groupe par ou une comparaison, il n'y a pas besoin de convertir en datetime . Si vous pouvez sauver quelques CPU en évitant cela, à moins que vous ayez besoin de la valeur finale pour l'affichage. Vous pouvez même Grouper par la valeur non convertie et mettre la conversion seulement dans la clause SELECT:

select Convert(datetime, DateDiff(dd, 0, Tm))
from (select '2010-09-12 00:00:00.003') X (Tm)
group by DateDiff(dd, 0, Tm)

aussi, voir comment les conversions numériques ne prennent que légèrement plus de temps pour revenir à datetime , mais la conversion varchar presque doubles? Cela révèle la partie du CPU qui est consacré au calcul de date dans les requêtes. Il y a des parties du CPU utilisation qui n'implique pas le calcul de date, et cela semble être quelque chose de proche de 19875 ms dans les requêtes ci-dessus. Puis la conversion prend une certaine quantité supplémentaire, donc s'il y a deux conversions, cette quantité est utilisée environ deux fois.

plus d'examen révèle que par rapport à Convert(, 112) , la requête Convert(, 101) a une certaine dépense CPU supplémentaire (depuis qu'il utilise un plus long varchar ?), parce que la deuxième conversion de nouveau en date ne coûte pas comme tout comme la conversion initiale en varchar , mais avec Convert(, 112) il est plus proche de la même 20000 ms CPU coût de base.

Voici les calculs sur le temps CPU que j'ai utilisé pour l'analyse ci-dessus:

     method   round  single   base
-----------  ------  ------  -----
       date   21324   19891  18458
        int   23031   21453  19875
   datediff   23782   23218  22654
      float   36891   29312  21733
varchar-112  102984   64016  25048
varchar-101  123375   65609   7843

• round est le temps CPU pour un aller-retour retour à datetime .

• unique est le temps CPU pour une conversion simple à l'autre type de données (celui qui a l'effet secondaire de supprimer la portion de temps).

• base est le calcul de la soustraction de single la différence entre les deux invocations: single - (round - single) . C'est un chiffre approximatif qui suppose la conversion vers et à partir de ce type de données et datetime est approximativement le même dans les deux sens. Il semble que cette hypothèse ne parfait mais est proche parce que les valeurs sont toutes proches de 20000 ms avec une seule exception.

une chose plus intéressante est que le coût de base est presque égal à la méthode simple Convert(date) (qui doit être presque 0 Coût, que le serveur peut extraire en interne la partie de jour entier droit sur les quatre premiers octets du datetime type de données).

Conclusion

donc, ce à quoi il ressemble, c'est que la méthode de conversion varchar prend environ 1,8 µs et la méthode DateDiff prend environ 0,18 µs. Je Base cela sur le temps de "base CPU" le plus conservateur dans mon test de 18458 ms total pour 25,920,000 lignes, donc 23218 ms / 25920000 = 0,18 µs. L'apparente amélioration 10x semble beaucoup, mais elle est franchement assez petite jusqu'à ce que vous ayez affaire à des centaines de milliers de lignes (617k lignes = 1 seconde économies).

même compte tenu de cette petite amélioration absolue, à mon avis, la méthode DateAdd gagne parce que c'est la meilleure combinaison de performance et de clarté. La réponse qui nécessite un "nombre magique" de 0.50000004 va mordre quelqu'un un jour (cinq zéros ou six???de plus, c'est plus difficile à comprendre.

Notes Complémentaires

quand j'aurai du temps, je changerai 0.50000004 pour '12:00:00.003' et voir comment il fait. Il est converti à la même valeur datetime et je le trouve beaucoup plus facile à se rappeler.

pour les personnes intéressées, les tests ci-dessus ont été exécutés sur un serveur où @ @ Version renvoie ce qui suit:

Microsoft SQL Server 2008 ( RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43: 34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)