Meilleure approche pour supprimer une partie du temps de datetime dans SQL Server
Quelle méthode fournit la meilleure performance lors de la suppression de la portion de temps d'un champ datetime dans SQL Server?
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
ou
b) select cast(convert(char(11), getdate(), 113) as datetime)
la seconde méthode envoie quelques octets de plus dans les deux sens, mais cela pourrait ne pas être aussi important que la vitesse de la conversion.
les deux semblent aussi être très rapides, mais il pourrait y avoir une différence de vitesse quand il s'agit de centaines de milliers ou plus de lignes?
aussi, est-il possible qu'il y ait des méthodes encore meilleures pour se débarrasser de la portion de temps d'une datetime en SQL?
23 réponses
Strictement, la méthode a
est le moins gourmand en ressource:
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
prouvé moins intensif en CPU pour la même durée totale un million de lignes par quelqu'un avec beaucoup trop de temps sur leurs mains: le moyen le plus efficace dans SQL Server pour obtenir date de date+heure?
j'ai vu un test similaire ailleurs, avec des résultats similaires.
je préfère le DATEADD / DATEDIFF parce que:
- varchar est soumise à la langue/dateformat questions
Exemple: Pourquoi mon expression de cas n'est-elle pas déterministe? - float s'appuie sur la mémoire de stockage interne
- il s'étend à travailler sur le premier jour du mois, demain etc en changeant "0" de base
Édition, Octobre 2011
pour SQL Server 2008+, vous pouvez lancer à date
. Ou utilisez juste date
donc pas le temps d'enlever.
Édition, Janvier 2012
un exemple travaillé de la façon dont flexible ce est: besoin de calculer par l'heure arrondie ou le chiffre de date dans le serveur sql
Édition, Mai 2012
ne pas utiliser cette clause dans les cas où des clauses et similaires sans penser: ajouter une fonction ou mouler à une colonne invalide l'utilisation des index. Voir numéro 2 ici: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes /
maintenant, cela a un exemple de versions plus tardives de L'optimiseur de Serveur SQL gérant CAST à date correctement, mais généralement ce sera une mauvaise idée ...
Modifier, Sep 2018, pour datetime2
DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'
select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)
dans SQL Server 2008, vous pouvez utiliser:
CONVERT(DATE, getdate(), 101)
bien sûr, c'est un vieux fil, mais pour le rendre complet.
de SQL 2008, vous pouvez utiliser le type de données de DATE donc vous pouvez simplement faire:
SELECT CONVERT(DATE,GETDATE())
dans SQL Server 2008, il y a un datetype (aussi un TIME datatype).
CAST(GetDate() as DATE)
ou
declare @Dt as DATE = GetDate()
Voici encore une autre réponse, d'un autre question en double:
SELECT CAST(CAST(getutcdate() - 0.50000004 AS int) AS datetime)
cette méthode de Nombre Magique exécute légèrement plus rapidement que la méthode DATEADD. (Il ressemble à ~10%)
Le Temps de calcul sur plusieurs tours d'un million d'enregistrements:
DATEADD MAGIC FLOAT
500 453
453 360
375 375
406 360
mais notez que ces chiffres ne sont peut-être pas pertinents car ils sont déjà très rapides. A moins que je n'ai des disques de 100.000 ou plus, je Je n'ai même pas eu le temps de lire au-dessus de zéro.
compte tenu du fait que DateAdd est destiné à cet effet et est plus robuste, je dirais utiliser DateAdd.
Bande de temps sur les insertions, mises à jour en premier lieu. Quant à la conversion à la volée, rien ne peut battre une fonction définie par l'utilisateur maintenabilité-Sage:
select date_only(dd)
l'implémentation de date_only
peut être tout ce que vous voulez - maintenant il est abstrait et appeler du code est beaucoup plus propre.
voir cette question:
Comment puis-je tronquer une datetime dans SQL Server?
quoi que vous fassiez, n'utilisez pas la méthode des cordes . C'est au sujet de la pire façon pour vous de le faire.
a déjà répondu mais je jetterai cela là aussi... cela préf ormit aussi suposément bien mais cela fonctionne en jetant la décimale (qui stocke le temps) du flotteur et en retournant seulement la partie entière (qui est date)
CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)
la deuxième fois j'ai trouvé cette solution... j'ai enlevé ce code
CAST(round(cast(getdate()as real),0,1) AS datetime)
cette méthode n'utilise pas de fonction de chaîne. Date
est essentiellement un type de données réel avec des chiffres avant décimales sont fraction d'un jour.
je suppose que ce sera plus rapide que beaucoup.
pour moi le code ci-dessous est toujours un gagnant:
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())));
attention!
Méthode a) et b) n'a pas toujours la même sortie!
select DATEADD(dd, DATEDIFF(dd, 0, '2013-12-31 23:59:59.999'), 0)
sortie: 2014-01-01 00:00:00.000
select cast(convert(char(11), '2013-12-31 23:59:59.999', 113) as datetime)
sortie: 2013-12-31 00:00:00.000
(testé sur MS SQL Server 2005 et 2008 R2)
EDIT: selon le commentaire D'Adam, cela ne peut pas se produire si vous lisez la valeur de date de la table, mais cela peut se produire si vous fournissez votre valeur de date sous forme littérale (exemple: comme paramètre d'une procédure stockée appelée via ADO.NET).
j'aime:
[date] = CONVERT(VARCHAR(10), GETDATE(), 120)
le code de format 120
forcera la date dans la norme ISO 8601:
'YYYY-MM-DD' or '2017-01-09'
très facile à utiliser en dplyr ( R
) et pandas ( Python
)!
je pense que vous voulez dire
cast(floor(cast(getdate()as float))as datetime)
réel est seulement 32 bits, et pourrait perdre certaines informations
C'est le plus rapide
cast(cast(getdate()+x-0.5 as int)as datetime)
...bien que seulement environ 10% plus rapide (about 0.49 microseconds CPU vs. 0.58)
cela a été recommandé, et prend le même temps dans mon test juste maintenant:
DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
en SQL 2008, la fonction SQL CLR est environ 5 fois plus rapide que L'utilisation D'une fonction SQL serait, à 1,35 microsecondes versus 6,5 microsections, ce qui indique une fonction beaucoup plus basse-appel au-dessus pour une fonction SQL CLR versus un UDF SQL simple.
dans SQL 2005, la fonction SQL CLR est 16 fois plus rapide, selon mes tests, par rapport à cette fonction lente:
create function dateonly ( @dt datetime )
returns datetime
as
begin
return cast(floor(cast(@dt as float))as int)
end
et select cast(cast my_datetime_field as date) as datetime)
? Il en résulte la même date, avec l'heure fixée à 00: 00, mais évite toute conversion au texte et évite également tout arrondi numérique explicite.
je pense que si vous vous en tenez strictement à TSQL
que c'est la façon la plus rapide de tronquer le temps:
select convert(datetime,convert(int,convert(float,[Modified])))
j'ai trouvé que cette méthode de troncature est environ 5% plus rapide que la méthode DateAdd
. Et cela peut être facilement modifié pour arrondir au jour le plus proche comme ceci:
select convert(datetime,ROUND(convert(float,[Modified]),0))
ici j'ai fait une fonction pour enlever certaines parties d'un datetime pour le serveur SQL. Usage:
- le Premier paramètre est la date à être dépouillé.
- Second param est un char:
- s: tours de secondes; supprime millisecondes
- m: tours de minutes; supprime les secondes et les millisecondes
- h: tours à heures; supprime les minutes, les secondes et les millisecondes.
- d: tours de jours; supprime les heures, les minutes, les secondes et les millisecondes.
- renvoie la nouvelle datetime
create function dbo.uf_RoundDateTime(@dt as datetime, @part as char)
returns datetime
as
begin
if CHARINDEX( @part, 'smhd',0) = 0 return @dt;
return cast(
Case @part
when 's' then convert(varchar(19), @dt, 126)
when 'm' then convert(varchar(17), @dt, 126) + '00'
when 'h' then convert(varchar(14), @dt, 126) + '00:00'
when 'd' then convert(varchar(14), @dt, 112)
end as datetime )
end
juste au cas où quelqu'un cherche ici une version Sybase car plusieurs des versions ci-dessus ne fonctionnaient pas
CAST(CONVERT(DATE,GETDATE(),103) AS DATETIME)
- testé dans I SQL v11 fonctionnant sur un serveur adaptatif 15.7
si possible, pour des choses spéciales comme celles-ci, j'aime utiliser les fonctions CLR.
dans ce cas:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime DateOnly(SqlDateTime input)
{
if (!input.IsNull)
{
SqlDateTime dt = new SqlDateTime(input.Value.Year, input.Value.Month, input.Value.Day, 0, 0, 0);
return dt;
}
else
return SqlDateTime.Null;
}
moi, personnellement, j'utilise presque toujours User Defined functions pour cela si je traite avec SQL Server 2005 (ou version inférieure), cependant, il faut noter qu'il y a des inconvénients spécifiques à utiliser UDF, surtout si on les applique à des clauses où (voir ci-dessous et les commentaires sur cette réponse pour plus de détails). Si vous utilisez SQL Server 2008 (ou une version plus récente) - voir ci-dessous.
en fait, pour la plupart des bases de données que je crée, j'ajoute ces UDF à droite près depuis que je sais qu'il y a 99% de chance que j'en ai besoin tôt ou tard.
- je en créer un pour "que de la date" et "heure" (bien que la date "seulement" une est de loin le plus utilisé des deux).
voici quelques liens vers une variété D'UDF liés à la date:
Essential SQL Server Date, Time and DateTime Functions
fonction Get Date Only
ce dernier lien montre pas moins de 3 façons différentes d'obtenir la date seulement une partie d'un champ datetime et mentionne quelques avantages et inconvénients de chaque approche.
si vous utilisez un UDF, il est à noter que vous devez essayer d'éviter D'utiliser L'UDF dans le cadre d'une clause WHERE dans une requête car cela entravera grandement l'exécution de la requête. La raison principale en est que l'utilisation D'un UDF dans une clause où rend cette clause comme Non-sargable , ce qui signifie que SQL Server ne peut plus utiliser un index avec cette clause afin d'améliorer la vitesse d'exécution des requêtes. En me référant à mon propre usage des UDF, j'utiliserai fréquemment la colonne de date" brute " dans la clause où, mais j'appliquerai L'UDF à la colonne sélectionnée. De cette façon, L'UDF n'est appliqué qu'à l'ensemble des résultats filtrés et non à chaque ligne de la table faisant partie du filtre.
bien sûr, de l'absolu meilleur approche pour cela , il faut utiliser SQL Server 2008 (ou une version plus récente) et séparer vos dates et heures , car le moteur de base de données SQL Server fournit alors nativement les composants date et heure individuels, et peut les interroger efficacement indépendamment sans avoir besoin d'un UDF ou d'un autre mécanisme pour extraire la date ou l'heure d'un type composite datetime.
j'utiliserais:
CAST
(
CAST(YEAR(DATEFIELD) as varchar(4)) + '/' CAST(MM(DATEFIELD) as varchar(2)) + '/' CAST(DD(DATEFIELD) as varchar(2)) as datetime
)
créant ainsi effectivement un nouveau champ à partir du champ date que vous avez déjà.