Comment supprimer la partie temps d'une valeur de date (SQL Server) ?

Question

Voici ce que j'utilise :

SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)

Je pense qu'il y a peut-être un moyen meilleur/plus élégant.

Exigences :

• Il doit être aussi rapide que possible (moins il y a de moulages, mieux c'est).
• Le résultat final doit être un type de date, et non une chaîne de caractères.

Answer 1

Qu'est-ce qui est vraiment le mieux ?

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

Les conversions de flotteurs ne sont pas exactes

Tout d'abord, j'éviterais de convertir les dates en valeurs flottantes, car la conversion n'est pas correcte. Vous pouvez vous en sortir en faisant l'opération de suppression du temps avec précision, mais je pense que c'est une mauvaise idée de l'utiliser parce que cela communique implicitement aux développeurs qu'il s'agit d'une opération sûre et qu'il n'y a pas de risque d'erreur. ce n'est pas . Jetez un coup d'œil :

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.

De plus, ce n'est même pas le moyen le plus rapide !

Preuve - Test de performance

Si vous voulez effectuer vous-même quelques tests pour voir comment les différentes méthodes se comparent réellement, vous aurez besoin de ce setup script 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 cette opération crée une table de 427,57 Mo dans votre base de données et que son exécution prendra entre 15 et 30 minutes. Si votre base de données est petite et qu'elle est réglée sur une croissance de 10 %, l'opération prendra plus de temps que si elle est d'abord assez grande.

Maintenant pour le test de performance réel script. Veuillez noter qu'il est intentionnel de ne pas renvoyer les lignes au client, car cela est follement coûteux sur 26 millions de lignes et masquerait les différences de performance entre les méthodes.

Résultats des performances

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;

Quelques analyses erronées

Quelques notes à ce sujet. Tout d'abord, si vous effectuez simplement un GROUP BY ou une comparaison, il n'est pas nécessaire de reconvertir en date. Vous pouvez donc économiser un peu de CPU en évitant cette opération, sauf si vous avez besoin de la valeur finale à des fins d'affichage. Vous pouvez même GROUPER PAR la valeur non convertie et placer la conversion uniquement 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)

De plus, vous voyez que les conversions numériques ne prennent qu'un peu plus de temps pour être reconverties en date, mais que la conversion varchar double presque ? Cela révèle la part du CPU qui est consacrée au calcul de la date dans les requêtes. Il y a des parties de l'utilisation du CPU qui n'impliquent pas le calcul de la date, et cela semble être quelque chose de proche de 19875 ms dans les requêtes ci-dessus. Ensuite, la conversion prend une certaine quantité supplémentaire, donc s'il y a deux conversions, cette quantité est utilisée environ deux fois.

Un examen plus approfondi révèle que par rapport à Convert(, 112) le Convert(, 101) a un coût supplémentaire en termes de CPU (puisqu'elle utilise une variable plus longue), car la seconde conversion en date ne coûte pas autant que la conversion initiale en variable, mais avec la requête Convert(, 112) il est plus proche du même coût de base de 20000 ms du CPU.

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

• rond est le temps CPU pour un aller-retour vers datetime.

• simple est le temps CPU pour une seule conversion vers le type de données alternatif (celui qui a pour effet secondaire de supprimer la partie temps).

• base est le calcul qui consiste à soustraire de round la différence entre les deux invocations : single - (round - single). Il s'agit d'un chiffre approximatif qui suppose que la conversion vers et depuis ce type de données et le datetime est approximativement la même dans les deux sens. Il semble que cette hypothèse ne soit pas parfaite mais qu'elle soit proche car les valeurs sont toutes proches de 20000 ms à une seule exception près.

Il est intéressant de noter que le coût de base est presque égal à celui de la méthode unique Convert(date) (dont le coût doit être presque nul, car le serveur peut extraire en interne la partie entière du jour à partir des quatre premiers octets du type de données datetime).

Conclusion

Il semble donc que la méthode de conversion varchar à sens unique prenne environ 1,8 μs et que la méthode DateDiff à sens unique prenne environ 0,18 μs. Je me base sur le temps " CPU de base " le plus conservateur de mes tests, à savoir 18458 ms au total pour 25 920 000 lignes, soit 23218 ms / 25920000 = 0,18 μs. L'amélioration apparente de 10x semble être beaucoup, mais elle est franchement assez faible jusqu'à ce que vous ayez affaire à des centaines de milliers de lignes (617k lignes = 1 seconde d'économie).

Même en tenant compte de cette petite amélioration absolue, à mon avis, la méthode DateAdd l'emporte car elle est la meilleure combinaison de performance et de clarté. La réponse qui exige un "chiffre magique" de 0,50000004 va mordre quelqu'un un jour (cinq zéros ou six ???), et elle est plus difficile à comprendre.

Notes supplémentaires

Quand j'aurai un peu de temps, je changerai 0.50000004 en '12:00:00.003' et je verrai ce que cela donne. Elle est convertie en la même valeur de date et je la trouve beaucoup plus facile à retenir.

Pour ceux qui sont intéressés, les tests ci-dessus ont été effectué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 sur Windows NT 5.2 (Build 3790 : Service Pack 2)

Les critiques sont les bienvenues. Je ne cherche pas à prouver ma méthode, mais à trouver la MEILLEURE méthode.

Answer 2

SQL Server 2008 dispose d'une nouvelle date type de données et cela simplifie ce problème à :

SELECT CAST(CAST(GETDATE() AS date) AS datetime)

Answer 3

Itzik Ben-Gan dans Calculs de DATETIME, partie 1 (SQL Server Magazine, février 2007) montre trois méthodes pour effectuer une telle conversion ( du plus lent au plus rapide ; la différence entre la deuxième et la troisième méthode est faible) :

SELECT CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

SELECT CAST(CAST(GETDATE() - 0.50000004 AS int) AS datetime)

Votre technique (casting à float ) est suggéré par un lecteur dans le numéro d'avril du magazine. Selon lui, elle a des performances comparables à celles de la deuxième technique présentée ci-dessus.

Answer 4

Votre CAST - FLOOR - CAST semble déjà être la méthode optimale, du moins sur MS SQL Server 2005.

D'autres solutions que j'ai vues ont une conversion en chaîne de caractères, par exemple Select Convert(varchar(11), getdate(),101) en eux, ce qui est plus lent d'un facteur 10.

Answer 5

SELECT CONVERT(VARCHAR(10),[VOTRE NOM DE COLONNE],105) [YOURTABLENAME]