Meilleure approche pour supprimer la partie du temps de datetime de SQL Server
La méthode qui fournit les meilleures performances lors de la suppression de la partie heure à partir 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 deuxième méthode est un peu plus d'octets de toute façon, mais qui pourrait ne pas être aussi important que la vitesse de la conversion.
Les deux semblent être de très rapide, mais il peut y avoir une différence de vitesse lorsque vous traitez avec des centaines de milliers ou plus de lignes?
Aussi, est-il possible qu'il existe de meilleures méthodes pour se débarrasser de la partie heure de datetime de SQL?
- J'ai essayé sur un million d'enregistrements dans une de mes tables de production et je ne pouvais pas obtenir une lecture précise sur les performances de toute façon. Les deux méthodes retourné exactement la même quantité de données que.
- la manipulation de la chaîne est mcuh plus intensif du CPU. DATEADD et DATEDIFF sont conçus pour optimiser l'utilisation de la représentation de stockage utilisé par SQL Server.
- Sur 18,000,000 lignes c'est ce que j'ai trouvé (SQL Server 2008): la Méthode b est d'environ 24% plus lente que la méthode a. CAST(ÉTAGE(CAST(getdate() en tant QUE FLOAT)) AS DATETIME), est de 3,5% plus lente que la méthode a. Méthode semble être un gagnant en ce qui concerne les performances. Merci à tous pour les réponses grands.
- Pourquoi diable ne pas SQL ont une fonction intégrée pour le faire quand même?!!
- SQL 2008 nouveau type de données de DATE va gérer cela.
- J'ai essayé de Coulée de la date à date et il est encore plus lente que la méthode b. Si vous n'avez pas besoin de stocker la partie heure puis ce type de données serait très bien fonctionner.
- double possible de le moyen le Plus efficace dans SQL Server pour obtenir la date à partir de la date+l'heure?
- Double de Comment faire pour supprimer la partie heure d'une valeur datetime (SQL Server)?
Vous devez vous connecter pour publier un commentaire.
Strictement, méthode
a
est la moins gourmande en ressources:Avéré moins consommateur d'UC pour la même durée totale d'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 la date à partir de la date+l'heure?
J'ai vu un test similaire ailleurs avec les mêmes résultats.
Je préfère le DATEADD/DATEDIFF parce que:
Exemple: Pourquoi est mon CAS, l'expression non-déterministe?
Modifier, Oct 2011
Pour SQL Server 2008+, vous pouvez lancer à
date
. Ou simplement l'utiliserdate
donc pas de temps à enlever.Modifier, Jan 2012
Un exemple de la façon flexible, c'est: Besoin de calculer arrondie de l'heure ou de la date figure dans sql server
Modifier, Mai 2012
Ne pas l'utiliser dans les clauses where et comme sans y penser: ajout d'une fonction ou d'une FONTE d'une colonne invalide l'utilisation des index. Voir le numéro 2 ici: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/
Maintenant, cela ne veut avoir un exemple de SQL Server optimiser la gestion des versions de FONTE à jour correctement, mais généralement ce sera une mauvaise idée ...
Modifier, Sep 2018, pour datetime2
DATE
données temps est odieusement restrictive à ce qu'il va vous permettre de faire face à des choses comme dateadd, datediff et d'interagir avec d'autres données de date/heure types. Dans ces cas, leDATEADD()
approche règne en roi.0218
au lieu de2018
de l'année et leDATEDIFF
partie de votre déclaration déclenche une exceptionThe conversion of a datetime2 data type to a datetime data type resulted in an out-of-range datetime value
Essayer:select DATEDIFF(dd, 0, convert(datetime2(0), '0218-09-12', 120))
SELECT DATEDIFF(dd, '19000101', convert(datetime2(0), '0218-09-12', 120))
Dans SQL Server 2008, vous pouvez utiliser:
datetime
à undate
, et donc votre solution efficace se résume simplementCONVERT(DATE,getdate())
, qui a déjà été proposé plus d'une fois.CAST(GETDATE() AS DATE)
ou strictement ANSICAST(CURRENT_TIMESTAMP AS DATE)
je pense que c'est inutile. Rester avec la première.De-sûr, c'est un vieux thread, mais pour le rendre complet.
À partir de SQL 2008, vous pouvez utiliser le type de données de DATE de sorte que vous pouvez tout simplement faire:
...est pas une bonne solution, par les commentaires ci-dessous.
Je voudrais supprimer cette réponse, mais je vais le laisser ici comme un contre-exemple puisque je pense que les commentateurs expliquent pourquoi ce n'est pas une bonne idée, c'est toujours utile.
Dans SQL Server 2008, il y a une DATE datetype (qui est aussi un TEMPS de type de données).
ou
Voici encore une autre réponse, à partir d'un autre double question:
Ce nombre magique méthode effectue légèrement plus vite que le DATEADD méthode. (Il ressemble à ~10%)
Le Temps de calcul sur plusieurs tours d'un million d'enregistrements:
Mais note que ces chiffres sont probablement pas pertinentes parce qu'elles sont déjà TRÈS rapide. Si je n'avais pas d'enregistrement de 100 000 habitants ou plus, je ne pouvais même pas eu le Temps de calcul de lire au-dessus de zéro.
Compte tenu du fait que DateAdd est destiné à cet effet et est plus robuste, je dirais que l'utilisation DateAdd.
'12:00:00.003'
qui, je pense, beaucoup mieux, mais.J'aime vraiment:
La
120
code de format forcera la date dans la norme ISO 8601:Super facile à utiliser dans dplyr (
R
) et les pandas (Python
)!MÉFIEZ-vous!
Méthode a) et b) n'ont PAS toujours le même résultat!
De sortie:
2014-01-01 00:00:00.000
De sortie:
2013-12-31 00:00:00.000
(Testé sur MS SQL Server 2005 et 2008 R2)
EDIT: Selon Adam commentaire, cela ne peut pas arriver si vous lisez la valeur de la date de la table, mais cela peut arriver si vous fournir votre date de valeur comme un littéral (exemple: tant que paramètre d'une procédure stockée appelée via ADO.NET).
DATETIME
colonne. Le niveau le plus élevé est .997 à Partir de: msdn.microsoft.com/en-us/library/ms187819.aspx vous allez voir que les valeurs sont arrondies à avoir la millième place de 0, 3, ou 7. L'OP ne verrez pas la valeur de votre test sur leurs tables.Bande de temps sur les insertions, mises à jour en premier lieu. Comme pour la volée de conversion, rien ne peut battre une fonction définie par l'utilisateur maintanability-sage:
La mise en œuvre de
date_only
peut être tout ce que vous voulez, maintenant, il disparaît et l'appel de code est beaucoup plus propre.WHERE DateAdd(DateDiff(Column)) = @DateValue
de ne pas utiliser un index. D'autre part,WHERE Column >= dbo.UDF(@DateValue) AND Column < dbo.UDF(@DateValue + 1)
est SARGable. Donc attention à la façon dont vous l'avez mis.Voir cette question:
Comment tronquer un datetime de SQL Server?
Quoi que vous fassiez, ne pas utiliser la méthode de chaîne. C'est au sujet de la pire façon pour vous de le faire.
Déjà répondu, mais malade de se débarrasser de cette là aussi...
cette suposedly aussi préformes bien, mais il fonctionne en supprimant la virgule (qui enregistre le temps) à partir du flotteur et le retour seul de toute la partie (qui date)
deuxième fois, j'ai trouvé cette solution... j'ai attrapé ce code
Cette méthode ne permet pas d'utiliser la fonction string.
Date
est fondamentalement un type de données réel avec des chiffres avant la virgule sont fraction de jour.je suppose que ce sera plus rapide que beaucoup.
Pour moi le code ci-dessous est toujours un gagnant:
select CONVERT(char(10), GetDate(),126)
Je pense que tu veux dire
cast(floor(cast(getdate()as float))as datetime)
réel n'est que de 32 bits, et pourrait perdre des informations
C'est plus rapide
cast(cast(getdate()+x-0.5 as int)as datetime)
...si seulement environ 10% plus rapide
(about 0.49 microseconds CPU vs. 0.58)
Cela a été recommandé, et prend même temps dans mon test tout à l'heure:
DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
Dans SQL server 2008, SQL CLR fonction est d'environ 5 fois plus rapide que l'utilisation d'une fonction SQL serait, à 1.35 microsecondes contre 6,5 microsections, indiquant beaucoup plus faible de la fonction appel de surcharge pour un SQL CLR fonction par rapport à une simple SQL UDF.
Dans SQL 2005, SQL CLR fonction est 16 fois plus rapide, selon mes tests, par rapport à cette lenteur de la fonction:
Comment sur
select cast(cast my_datetime_field as date) as datetime)
? Il en résulte la même date, avec le réglage de l'heure à 00:00, mais évite tout de conversion de texte et évite aussi explicite numériques arrondi.Je pense que si vous vous en tenez strictement à
TSQL
que c'est le moyen le plus rapide pour tronquer le temps:J'ai trouvé cette méthode de troncature à environ 5% plus rapide que la
DateAdd
méthode. Et cela peut être facilement modifié pour la ronde de la plus proche à jour comme ceci:Ici j'ai fait une fonction pour supprimer certaines parties d'un datetime de SQL Server. Utilisation:
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 est à la recherche de ici pour une Sybase version depuis plusieurs versions ci-dessus ne fonctionne pas
cast
: Pour SQL Server 2008+, vous pouvez lancer jusqu'à présent. Ou tout simplement utiliser date donc pas de temps à supprimer.datetime
àdate
: aucun de ceux qui est inhérente au format.Si possible, pour des choses spéciales comme cela, j'aime bien utiliser des fonctions CLR.
Dans ce cas:
J'ai, personnellement, presque toujours utiliser Fonctions Définies par l'utilisateur pour cela, si vous traitez avec SQL Server 2005 (ou version inférieure), cependant, il convient de noter qu'il y a des inconvénients à l'utilisation de l'UDF, surtout si les appliquant à des clauses where (voir ci-dessous et les commentaires sur cette réponse pour plus de détails). Si vous utilisez SQL Server 2008 (ou plus) - voir ci-dessous.
En fait, pour la plupart des bases de données que j'ai créé, j'ajoute ces UDF est en droit près du départ, puisque je sais qu'il y a 99% de chance je vais avoir besoin d'eux tôt ou tard.
- Je en créer un pour "que de la date" & "le temps seulement" (bien que la date "seulement" une est de loin le plus utilisé des deux).
Voici quelques liens vers diverses liées à la date de l'UDF, de l':
Essentiel SQL Server Date, l'Heure et les Fonctions date et heure
Obtenir La Date Seule Fonction
Que dernier maillon montre pas moins de 3 manières différentes à la date de l'obtention seulement une partie d'un champ datetime et mentionne certains des avantages et des inconvénients de chaque approche.
Si vous utilisez un UDF, il convient de noter que vous devriez essayer d'éviter à l'aide de l'UDF dans le cadre d'une clause where dans une requête que cela va grandement nuire aux performances de la requête. La principale raison pour cela est que l'utilisation d'une fonction dans une clause where rend cette clause 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 de la requête. En référence à ma propre utilisation de l'UDF, je vais souvent utiliser la "première" date de colonne dans la clause where, mais s'appliquent à l'UDF de la colonne Sélectionnée. De cette façon, l'UDF n'est appliquée que sur les résultats filtré et pas à chaque ligne de la table en tant que partie du filtre.
Bien sûr, l'absolu meilleur pour cette approche est l'utilisation de SQL Server 2008 (ou supérieur) et de répartir vos les dates et les heures, comme la base de données SQL Server moteur est alors en mode natif donner à l'individu la date et l'heure de composants, et peut efficacement une demande de manière indépendante, sans la nécessité d'une fonction ou d'un autre mécanisme pour extraire la date ou l'heure de la partie à partir d'un composite de type datetime.
WHERE DateColumn >= {TimeTruncatingExpression}(@DateValue) AND DateColumn < {TimeTruncatingExpression}(@DateValue + 1)
. J'ai senti que je devais dire quelque chose puisque l'on vous dit "j'ai presque toujours utiliser des fonctions définies par l'utilisateur" ne pas expliquer tous les inconvénients, ni le moyen de mettre une date-requête ne SARGable.Je voudrais utiliser:
Ainsi efficacement la création d'un nouveau champ dans le champ de la date que vous avez déjà.
datetime
valeur, en les convertissant à cordes, la concaténation de ces ensemble et enfin la conversion du résultat en arrière àdatetime
est mieux que, par exemple, d'effectuer des calculs directs sur l'originaldatetime
(leDATEADD
/DATEDIFF
méthode)?MM
etDD
? Il n'y a pas de telles fonctions dans SQL Server.