Il modo più efficiente in SQL Server per ottenere la data dalla data + ora?

In MS SQL 2000 e 2005, dato un datetime come ‘2008-09-25 12:34:56’ qual è il modo più efficiente per ottenere un datetime contenente solo ‘2008-09-25’?

Duplicato qui

Devo ammettere che non avevo visto la conversione float da pavimento mostrata prima da Matt. Ho dovuto testare questo.

Ho provato una selezione pura (che restituirà data e ora, e non è quello che vogliamo), la soluzione regnante qui (floor-float), un ‘ingenuo’ comune menzionato qui (stringconvert) e quello menzionato qui che ero usando (come pensavo fosse il più veloce).

Ho testato le query su un server di prova MS SQL Server 2005 in esecuzione su un server Win 2003 SP2 con una CPU Xeon 3GHz in esecuzione su memoria massima (32 bit, quindi è circa 3,5 GB). È notte in cui sono così che la macchina gira al minimo quasi senza carico. Ho tutto per me.

Ecco il log del mio test-run che seleziona da una grande tabella contenente timestamp che varia al millisecondo. Questo set di dati particolare include date che vanno oltre 2,5 anni. Il tavolo stesso ha oltre 130 milioni di righe, ecco perché mi limito al primo milione.

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 

Analisi SQL Server e tempo di compilazione: tempo CPU = 0 ms, tempo trascorso = 1 ms.

(1000000 righe interessate) Tabella ‘tblMeasureLogv2’. Conteggio scansioni 1, letture logiche 4752, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server: tempo CPU = 422 ms, tempo trascorso = 33803 ms.

(1000000 righe interessate) Tabella ‘tblMeasureLogv2’. Conteggio scansioni 1, letture logiche 4752, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server: tempo CPU = 625 ms, tempo trascorso = 33545 ms.

(1000000 righe interessate) Tabella ‘tblMeasureLogv2’. Conteggio scansioni 1, letture logiche 4752, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server: tempo CPU = 1953 ms, tempo trascorso = 33843 ms.

(1000000 righe interessate) Tabella ‘tblMeasureLogv2’. Conteggio scansioni 1, letture logiche 4752, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server: tempo CPU = 531 ms, tempo trascorso = 33440 ms. Analisi SQL Server e tempo di compilazione: tempo CPU = 0 ms, tempo trascorso = 1 ms.

Tempi di esecuzione di SQL Server: tempo CPU = 0 ms, tempo trascorso = 1 ms.

Cosa stiamo vedendo qui?

Concentriamoci sul tempo della CPU (stiamo guardando alla conversione), e possiamo vedere che abbiamo i seguenti numeri:

 Pure-Select: 422 Floor-cast: 625 String-conv: 1953 DateAdd: 531 

Da questo mi sembra che DateAdd (almeno in questo caso particolare) sia leggermente più veloce del metodo cast a pavimento.

Prima di andare lì, ho eseguito questo test più volte, con l’ordine delle query cambiate, risultati uguali.

È qualcosa di strano sul mio server o cosa?

 Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) 

DateDiff (Day, 0, GetDate ()) è uguale a DateDiff (Day, ‘1900-01-01’, GetDate ())

Dato che DateDiff restituisce un intero, si otterrà il numero di giorni trascorsi dal 1 gennaio 1900. Quindi si aggiunge il numero intero di giorni a Gen 1, 1900. L’effetto netto sta rimuovendo il componente orario.

Vorrei anche ricordare che questo metodo funziona per qualsiasi parte data / ora (come anno, trimestre, mese, giorno, ora, minuto e secondo).

 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') 

L’ultimo, per secondi, richiede una gestione speciale. Se usi Jan 1, 1900 riceverai un errore.

La differenza tra due colonne datetime ha causato un overflow in fase di esecuzione.

È ansible aggirare questo errore utilizzando una data di riferimento diversa (ad esempio 1 gennaio 2000).

 select cast(floor(cast(@datetime as float)) as datetime) 

Funziona perché il casting di un datetime per il float fornisce il numero di giorni (comprese le frazioni di un giorno) dal 1 gennaio 1900. Il Flooring rimuove i giorni frazionari e lascia il numero di giorni interi, che possono quindi essere riportati a un datetime.

in SQL Server 2012 utilizzare

 select cast(getdate() as date) 
 select cast(getdate()as varchar(11))as datetime 

Per ottenere AAAA-MM-GG, utilizzare:

 select convert(varchar(10), getdate(), 120) 

Modifica: Oops, vuole un DateTime invece di una stringa. L’equivalente di TRUNC () in Oracle. Puoi prendere ciò che ho postato e restituire a un DateTime:

 select convert(datetime, convert(varchar(10), getdate(), 120) , 120) 

CONVERT, FLOOR e DATEDIFF si comportano allo stesso modo.

Come restituire la parte di data solo da un tipo di dati datetime di SQL Server

Tre metodi descritti nel link sottostante. Non ho provato le prestazioni per determinare quale sia il più veloce.

http://www.blackwasp.co.uk/SQLDateFromDateTime.aspx

CAST(FLOOR(CAST(yourdate AS DECIMAL(12, 5))) AS DATETIME) il migliore in CAST(FLOOR(CAST(yourdate AS DECIMAL(12, 5))) AS DATETIME) . è ansible visualizzare le prove e i test quando si ottiene la data senza orario in SQL Server

Informazioni su SELECT CAST(CASt(GETDATE() AS int) AS DATETIME) ??

 CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]