Le moyen le plus efficace dans SQL Server pour obtenir la date de date+heure?
11 réponses
je dois admettre que je n'avais jamais vu la conversion sol-flotteur montré par Matt avant. J'ai eu de la tester.
j'ai testé un pur select (qui va revenir Date et heure, et n'est pas ce que nous voulons), la solution qui règne ici (floor-float), un commun 'naïf' celui mentionné ici (stringconvert) et celui mentionné ici que j'utilisais (comme je pensais qu'il était le plus rapide).
j'ai testé les requêtes sur un serveur de test MS SQL Server 2005 tournant sur un Win Serveur SP2 2003 avec un CPU xeon 3GHz tournant sur la mémoire max (32 bits, soit environ 3,5 Go). C'est la nuit où je suis donc la machine tourne au ralenti presque sans charge. Je l'ai pour moi tout seul.
voici le journal de mon essai de sélection à partir d'une grande table contenant des horodateurs variant jusqu'au niveau milliseconde. Cet ensemble de données comprend des dates s'échelonnant sur plus de 2,5 ans. La table elle-même a plus de 130 millions de rangs, c'est pourquoi je me limite au million supérieur.
SELECT TOP 1000000 CRETS FROM tblMeasureLogv2
SELECT TOP 1000000 CAST(FLOOR(CAST(CRETS AS FLOAT)) AS DATETIME) FROM tblMeasureLogv2
SELECT TOP 1000000 CONVERT(DATETIME, CONVERT(VARCHAR(10), CRETS, 120) , 120) FROM tblMeasureLogv2
SELECT TOP 1000000 DATEADD(DAY, DATEDIFF(DAY, 0, CRETS), 0) FROM tblMeasureLogv2
SQL Server parle et compile le temps: temps CPU = 0 ms, temps écoulé = 1 ms.
(1000000 ligne (s) affectée (s)) tableau "tblmeesurelogv2". Analyse nombre 1, lectures logiques 4752, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures anticipées lob 0.
temps D'exécution du serveur SQL: temps CPU = 422 ms, temps écoulé = 33803 ms.
(1000000 ligne (s) touchée (s)) tableau "tblMeasureLogv2". Analyse nombre 1, lectures logiques 4752, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures anticipées lob 0.
temps D'exécution du serveur SQL: temps CPU = 625 ms, temps écoulé = 33545 ms.
(1000000 ligne (s) affectée (s)) tableau "tblmeesurelogv2". Analyse nombre 1, lectures logiques 4752, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lob lectures anticipées 0.
SQL Server Execution Times: CPU time = 1953 ms, overtime = 33843 ms.
(1000000 ligne (s) affectée (s)) tableau "tblmeesurelogv2". Analyse nombre 1, lectures logiques 4752, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures anticipées lob 0.
temps D'exécution du serveur SQL: temps CPU = 531 ms, temps écoulé = 33440 ms. SQL Server analyse et Compilation time: CPU temps = 0 ms, temps écoulé = 1 ms.
temps D'exécution du serveur SQL: temps CPU = 0 ms, temps écoulé = 1 ms.
Que voyons-nous ici?
concentrons-nous sur le temps CPU (nous regardons la conversion), et nous pouvons voir que nous avons les nombres suivants:
Pure-Select: 422
Floor-cast: 625
String-conv: 1953
DateAdd: 531
de ceci il me semble que le DateAdd (au moins dans ce cas particulier) est légèrement plus rapide que le fond-coulée méthode.
avant d'aller là-bas, j'ai fait ce test plusieurs fois, avec l'ordre des requêtes changé, même résultat.
Est-ce quelque chose d'étrange sur mon serveur, ou quoi?
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
DateDiff(Jour, 0, GetDate()) est la même que DateDiff(Jour, '1900-01-01', GetDate())
puisque DateDiff renvoie un entier, vous obtiendrez le nombre de jours qui se sont écoulés depuis le 1er janvier 1900. Vous ajoutez ensuite ce nombre entier de jours au 1er janvier 1900. L'effet net est d'enlever la composante temporelle.
je devrais également mentionner que cette méthode fonctionne pour n'importe quelle partie de date/heure (comme l'année, le trimestre, le mois, le jour, l'heure, la minute, et deuxième.)
Select DateAdd(Year, DateDiff(Year, 0, GetDate()), 0)
Select DateAdd(Quarter, DateDiff(Quarter, 0, GetDate()), 0)
Select DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
Select DateAdd(Hour, DateDiff(Hour, 0, GetDate()), 0)
Select DateAdd(Second, DateDiff(Second, '20000101', GetDate()), '20000101')
le dernier, pendant quelques secondes, nécessite une manipulation spéciale. Si vous utilisez Jan 1, 1900, vous obtiendrez une erreur.
la différence de deux colonnes datetime a causé un débordement à l'exécution.
vous pouvez contourner cette erreur en utilisant une date de référence différente (comme le 1er janvier 2000).
select cast(floor(cast(@datetime as float)) as datetime)
fonctionne parce que la coulée d'une datetime pour flotter donne le nombre de jours (y compris les fractions d'un jour) depuis le 1er janvier 1900. Le plancher élimine les jours fractionnels et laisse le nombre de jours entiers, qui peuvent ensuite être moulés à une date.
pour obtenir AAAA-MM - JJ, utiliser:
select convert(varchar(10), getdate(), 120)
Edit: Oups, il veut un DateTime au lieu d'une chaîne. L'équivalent de TRUNC () dans Oracle. Vous pouvez prendre ce que j'ai posté et le cast en arrière à une DateTime:
select convert(datetime, convert(varchar(10), getdate(), 120) , 120)
CONVERTIR, à l'ÉTAGE ,et DATEDIFF effectuera de la même façon.
Comment retourner la partie date seulement à partir d'un Serveur SQL de type de données datetime
Trois méthodes décrites dans le lien ci-dessous. Je ne les ai pas testés pour déterminer ce qui est le plus rapide.
CAST(FLOOR(CAST(yourdate AS DECIMAL(12, 5))) AS DATETIME) réalise le meilleur et de loin. vous pouvez voir la preuve et les tests quand obtenir la date sans temps dans le serveur sql