risposta-alla-domanda-sullo-sviluppo-web-bd.com

Come posso troncare un datetime in SQL Server?

Qual è il modo migliore per troncare un valore datetime (in modo da rimuovere ore e secondi in secondi) in SQL Server 2008?

Per esempio:

declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)

-----------------------
2009-05-28 00:00:00.000
246
Julio César

Questo continua a raccogliere spesso voti aggiuntivi, anche diversi anni dopo, e quindi ho bisogno di aggiornarlo per le versioni moderne di Sql Server. Per Sql Server 2008 e versioni successive, è semplice:

cast(getDate() As Date)

Nota che gli ultimi tre paragrafi vicino al fondo si applicano ancora, e spesso devi fare un passo indietro e trovare un modo per evitare il cast in primo luogo.

Ma ci sono anche altri modi per farlo. Ecco i più comuni.

Il modo corretto (nuovo da Sql Server 2008):

cast(getdate() As Date)

Il modo corretto (vecchio):

dateadd(dd, datediff(dd,0, getDate()), 0)

Questo è più vecchio ora, ma vale comunque la pena conoscere perché può anche facilmente adattarsi per altri punti temporali, come il primo momento del mese, minuto, ora o anno.

Questo modo corretto utilizza funzioni documentate che fanno parte dello standard ansi e sono garantite per funzionare, ma può essere un po 'più lento. Funziona trovando quanti giorni ci sono dal giorno 0 al giorno corrente, e aggiungendo che molti giorni indietro al giorno 0. Funzionerà indipendentemente dal modo in cui è archiviato il datetime e non importa quale sia la tua localizzazione.

Il modo veloce:

cast(floor(cast(getdate() as float)) as datetime)

Funziona perché le colonne datetime sono archiviate come valori binari a 8 byte. Lanciali a galleggiare, pavimentali per rimuovere la frazione e la porzione temporale dei valori se ne va quando li trasmetti a datetime. È tutto solo un po 'spostato senza una logica complicata ed è very veloce. 

Essere consapevoli del fatto che si basa su un dettaglio di implementazione Microsoft è libero di cambiare in qualsiasi momento, anche in un aggiornamento automatico del servizio. Inoltre, non è molto portatile. In pratica, è molto improbabile che questa implementazione cambierà presto, ma è comunque importante essere consapevoli del pericolo se si sceglie di usarlo. E ora che abbiamo l'opzione di lanciare una data, è raramente necessario.

La strada sbagliata:

cast(convert(char(11), getdate(), 113) as datetime)

Il modo sbagliato funziona convertendosi in una stringa, troncando la stringa e convertendola in un datetime. È wrong, per due motivi: 1) potrebbe non funzionare su tutte le impostazioni locali e 2) si tratta del modo più lento possibile per farlo ... e non solo un po '; è come un ordine di grandezza o due più lento rispetto alle altre opzioni. 


Aggiornamento Ultimamente ha ottenuto alcuni voti, e quindi voglio aggiungere che da quando ho postato questo ho visto alcune prove abbastanza evidenti che Sql Server ottimizzerà la differenza di prestazioni tra il modo "corretto" e il modo "veloce", nel senso che ora dovresti favorire il primo.

In entrambi i casi, si desidera scrivere le query per evitare di doverlo fare in primo luogo. È molto raro che tu faccia questo lavoro sul database. 

Nella maggior parte dei casi, il database è già il collo di bottiglia. Generalmente è il server il più costoso da aggiungere all'hardware per migliorare le prestazioni e quello più difficile da ottenere correttamente (è necessario bilanciare i dischi con la memoria, ad esempio). È anche il più difficile da scalare verso l'esterno, sia dal punto di vista tecnico che da quello commerciale; è molto più semplice tecnicamente aggiungere un server web o applicativo rispetto a un server database e anche se ciò fosse falso non si pagano $ 20.000 + per licenza server per IIS o Apache. 

Il punto che sto cercando di fare è che, quando possibile, dovresti fare questo lavoro a livello di applicazione. Il tempo di only che dovresti mai trovare per troncare un datetime su Sql Server è quando devi raggruppare entro il giorno, e anche in questo caso dovresti probabilmente avere una colonna aggiuntiva configurata come una colonna calcolata, mantenuta ad insert/update tempo o mantenuto nella logica dell'applicazione. Prendi questo lavoro che rompe gli indici, a causa della CPU, dal tuo database.

443
Joel Coehoorn

Solo per SQL Server 2008

CAST(@SomeDateTime AS Date) 

Quindi, se lo desideri, ricondurlo a datetime

CAST(CAST(@SomeDateTime AS Date) As datetime)
44
DJ.

Solo per una risposta più completa, ecco un modo efficace per troncare una qualsiasi parte della data verso il basso e minuti compresi (sostituire GETDATE() con la data da troncare).

Questo è diverso dalla risposta accettata in quanto è possibile utilizzare non solo dd (giorni), ma qualsiasi parte della data (vedere qui ):

dateadd(minute, datediff(minute, 0, GETDATE()), 0)

Nota che nell'espressione sopra, 0 è una data costante all'inizio di un anno (1900-01-01). Se è necessario troncare parti più piccole, ad esempio secondi o millisecondi, è necessario prendere una data costante più vicina alla data da troncare per evitare un overflow.

20
Lucero

Il frammento che ho trovato sul web quando dovevo fare questo era:

 dateadd(dd,0, datediff(dd,0, YOURDATE))
 e.g.
 dateadd(dd,0, datediff(dd,0, getDate()))
7
Tom Ritter
CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)
1
Dean

In SQl 2005 la tua funzione trunc_date potrebbe essere scritta in questo modo.

(1)

CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
    CAST(FLOOR( CAST( @date AS FLOAT ) )AS DATETIME)
END

Il primo metodo è molto più pulito. Usa solo 3 chiamate di metodo incluso il finale CAST () e non esegue concatenazioni di stringhe, che è un plus automatico. Inoltre, non ci sono calchi di tipo enorme qui. Se puoi immaginare che i timbri Data/Ora possono essere rappresentati, la conversione da date a numeri e viceversa è un processo abbastanza semplice.

(2)

CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
      SELECT CONVERT(varchar, @date,112)
END

Se sei preoccupato dell'implementazione di datetimes (2) o (3) da parte di Microsoft, potrebbe essere ok.

(3)

CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
SELECT CAST((STR( YEAR( @date ) ) + '/' +STR( MONTH( @date ) ) + '/' +STR( DAY(@date ) )
) AS DATETIME
END

In terzo luogo, il metodo più dettagliato. Ciò richiede di suddividere la data nelle parti dell'anno, del mese e del giorno, riunendole nel formato "aaaa/mm/gg", quindi riportandole a una data. Questo metodo include 7 chiamate di metodo incluso il finale CAST (), per non parlare della concatenazione di stringhe.

1
AlejandroR

puoi semplicemente farlo (SQL 2008):

dichiara @SomeDate date = getdate ()

select @SomeDate

2009-05-28

seleziona cast (floor (cast (getdate () come float)) come datetime) Fai riferimento a questo: http://microsoftmiles.blogspot.com/2006/11/remove-time-from-datetime-in-sql -server.html

0
Sudhir Bastakoti

Oracolo: 

TRUNC(SYSDATE, 'MONTH')

Server SQL: 

DATEADD(DAY, - DATEPART(DAY, DateField) + 1, DateField)

Potrebbe essere usato allo stesso modo per troncare minuti o ore da una data.

0
Markus

Per quelli di voi che sono venuti qui alla ricerca di un modo per troncare un campo DATETIME a qualcosa in meno di un giorno intero, ad esempio ogni minuto, è possibile utilizzare questo:

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) + (FLOOR((CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 1440.0) + (3.0/86400000.0)) / 1440.0 AS DATETIME)

quindi se oggi fosse 2010-11-26 14:54:43.123 allora questo restituirebbe 2010-11-26 14:54:00.000.

Per modificare l'intervallo in cui trucca, sostituire 1440.0 con il numero di intervalli in un giorno, ad esempio:

24hrs          =   24.0  (for every hour)
24hrs / 0.5hrs =   48.0  (for every half hour)
24hrs / (1/60) = 1440.0  (for every minute)

(Inserisci sempre un .0 alla fine per trasmettere implicitamente a un float.)


Per quelli di voi che si chiedono cosa sia il (3.0/86400000) nel mio calcolo, sembra che SQL Server 2005 non trasmetta accuratamente da FLOAT a DATETIME, quindi aggiunge 3 millisecondi prima di renderlo disponibile.

0
BG100

Questa query dovrebbe fornire risultati equivalenti a trunc(sysdate) in Oracle.

SELECT  * 
FROM    your_table
WHERE   CONVERT(varchar(12), your_column_name, 101)
      = CONVERT(varchar(12), GETDATE(), 101)

Spero che questo ti aiuti!

0
Sandeep Gaadhe

È inoltre possibile estrarre la data using Substring dalla variabile datetime e il reinvio in data/ora ignorerà la parte relativa all'ora.

declare @SomeDate datetime = '2009-05-28 16:30:22'
SELECT cast(substring(convert(varchar(12),@SomeDate,111),0,12) as Datetime) 

Inoltre, puoi accedere a parti della variabile datetime e unirle a una data troncata del costrutto, qualcosa del genere:

SELECT cast(DATENAME(year, @Somedate) + '-' + 
       Convert(varchar(2),DATEPART(month, @Somedate)) + '-' +
       DATENAME(day, @Somedate) 
       as datetime)
0
NeverHopeless