Voici ce que j'utilise:
SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)
Je pense qu'il peut y avoir une façon meilleure et plus élégante.
Exigences:
datetimetype pas une chaîne.
SQL Server 2008 et jusqu'à
Dans SQL Server 2008 et plus, bien sûr , le moyen le plus rapide est Convert(date, @date). Cela peut être jeté en arrière à un datetimeou datetime2si nécessaire.
Ce qui est vraiment le meilleur dans SQL Server 2005 et plus?
Je l'ai vu réclamations contradictoires sur ce qui est le plus rapide pour tronquer le temps d'une date dans SQL Server, et certaines personnes ont même dit qu'ils ont fait des tests, mais mon expérience a été différente. Alors, faisons des tests plus rigoureux et que tout le monde a le script si je fais des erreurs que les gens peuvent me corriger.
Les conversions de flotteur ne sont pas exactes
Tout d' abord, je resterais loin de la conversion datetimeà float, car il ne convertit pas correctement. Vous pouvez sortir avec faire la chose du temps d'élimination avec précision, mais je pense qu'il est une mauvaise idée de l' utiliser , car il communique implicitement aux développeurs que c'est une opération sûre et ce n'est pas . Regarde:
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 les gens dans notre code ou dans nos exemples en ligne.
En outre, il est même pas le moyen le plus rapide!
Preuve - Test de performance
Si vous souhaitez effectuer des tests vous-même pour voir comment les différentes méthodes ne vraiment empiler, alors vous aurez besoin de ce script d'installation 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
S'il vous plaît noter que cela crée une table 427,57 Mo dans votre base de données et prendra quelque chose comme 15-30 minutes pour courir. Si votre base de données est petit et mis à 10% de croissance, il faudra plus de temps que si vous modifiez la taille assez grande première.
Maintenant, pour le script de test de performances réelles. S'il vous plaît noter qu'il est résolu à ne pas renvoyer les lignes au client car cela est fou cher de 26 millions de lignes et se cacher 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;
Quelques analyses Rambling
Quelques notes à ce sujet. Si juste d' effectuer un GROUP BY ou une comparaison, tout d'abord, il n'y a pas besoin de reconvertir datetime. Ainsi , vous pouvez économiser CPU en évitant que, à moins que vous avez besoin de la valeur finale à des fins d'affichage. Vous pouvez même GROUP BY la valeur non convertie et mettre la conversion que 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)
En outre, voir comment les conversions numériques prennent seulement un peu plus de temps pour reconvertir datetime, mais la varcharconversion double presque? Cela révèle la partie de la CPU qui est consacré au calcul de la date dans les requêtes. Il y a des parties de l'utilisation du processeur qui ne concernent pas le calcul de la date, et cela semble être quelque chose près de 19875 ms dans les requêtes ci - dessus. Ensuite , la conversion prend un certain montant supplémentaire, donc s'il y a deux conversions, ce montant est utilisé jusqu'à environ deux fois.
Un examen plus révèle que par rapport à Convert(, 112)la Convert(, 101)requête a une charge CPU supplémentaire (car il utilise une plus longue varchar?), Parce que la deuxième reconversion datene coûte pas autant que la conversion initiale varchar, mais Convert(, 112)il est plus proche de la même 20000 ms coût de base 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
tour est le temps CPU pour un aller - retour en arrière à datetime.
unique est le temps CPU pour une seule conversion du type de données de remplacement (celui qui a l'effet secondaire de l' enlèvement de la partie de temps).
la base est le calcul de la soustraction de singlela différence entre les deux invocations: single - (round - single). Il est un chiffre approximatif qui assume la conversion et à partir de ce type de données et datetimeest approximativement la même dans les deux sens. Il semble que cette hypothèse est pas parfait , mais est proche car les valeurs sont proches de 20000 ms avec une seule exception.
Une chose plus intéressante est que le coût de base est presque égale à la seule Convert(date)méthode (qui doit être presque 0 coût, que le serveur peut extraire en interne la partie jour entier dès la sortie des quatre premiers octets du datetimetype de données).
Conclusion
Alors à quoi il ressemble est que la seule direction varcharméthode de conversion prend environ 1,8 ms et la seule direction DateDiffméthode prend environ 0,18 ms. Je fonde cela sur le plus conservateur « CPU de base » temps dans mes tests de 18458 ms total de 25.920.000 lignes, de sorte que 23218 ms / 25920000 = 0,18 ms. L'amélioration apparente 10x semble beaucoup, mais il est franchement assez petit jusqu'à ce que vous faites affaire avec des centaines de milliers de lignes (617k lignes = 1 seconde épargne).
Même compte tenu de cette petite amélioration absolue, à mon avis, la DateAddméthode gagne parce qu'elle est la meilleure combinaison de performance et de clarté. La réponse qui a besoin d' un « nombre magique » de 0.50000004va mordre quelqu'un un jour (cinq ou six zéros ???), plus il est plus difficile à comprendre.
Notes complémentaires
Quand je reçois un peu de temps je vais changer 0.50000004pour '12:00:00.003'voir comment il le fait. Il est converti en la même datetimevaleur et je trouve beaucoup plus facile de se rappeler.
Pour les intéressés, les tests ci-dessus ont été exécutées sur un serveur où @@ version renvoie les éléments suivants:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 9 juillet 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition sur Windows NT 5.2 (Build 3790: Service Pack 2)
SQL Server 2008 a une nouvelle date de type de données , ce qui simplifie ce problème:
SELECT CAST(CAST(GETDATE() AS date) AS datetime)
Itzik Ben-Gan dans les calculs DATETIME, Partie 1 (SQL Server Magazine, Février 2007) montre trois méthodes d'exécution d' une telle conversion ( le plus lent au plus rapide , la différence entre les deuxième et troisième méthodes 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 (coulée à flotter ) est suggéré par un lecteur dans le numéro d' Avril du magazine. Selon lui, il a des performances comparables à celle de la seconde technique présentée ci - dessus.
Votre CAST- FLOOR- CASTsemble déjà être la meilleure façon, au moins sur MS SQL Server 2005.
D'autres solutions que j'ai vu ont une chaîne de conversion, comme Select Convert(varchar(11), getdate(),101)en eux, ce qui est plus lent par un facteur de 10.
S'il vous plaît essayez:
SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
SQL2005: Je vous recommande de jeter à la place dateadd. Par exemple,
select cast(DATEDIFF(DAY, 0, datetimefield) as datetime)
en moyenne environ 10% plus rapide sur mon jeu de données, que
select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0)
(Et la coulée en smalldatetime était encore plus rapide)