Como posso truncar um datetime no servidor SQL?
Qual é a melhor maneira de truncar um valor datetime (como remover horas minutos e segundos) no servidor SQL 2008?
por exemplo:
declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)
-----------------------
2009-05-28 00:00:00.000
13 answers
cast(getDate() As Date)
Note que os últimos três parágrafos perto do fundo ainda se aplicam, e você muitas vezes precisa dar um passo atrás e encontrar uma maneira de evitar o elenco em primeiro lugar.
Mas há outras maneiras de conseguir isso também. Aqui estão os mais comuns.A forma correcta (novo desde o servidor Sql 2008):
cast(getdate() As Date)
A maneira correta (antigo):
dateadd(dd, datediff(dd,0, getDate()), 0)
Isto é mais antigo agora, mas ainda vale a pena saber porque também pode adaptar-se facilmente para outros pontos de tempo, como o primeiro momento do mês, minuto, hora ou ano.
Esta maneira correta usa funções documentadas que fazem parte do padrão ansi e são garantidas para funcionar, mas pode ser um pouco mais lento. Funciona encontrando quantos dias Há do dia 0 ao dia atual, e adicionando há tantos dias atrás no dia 0. Ele vai funcionar não importa como o seu datetime é armazenado e não importa qual o seu local é.
O caminho rápido:
cast(floor(cast(getdate() as float)) as datetime)
Isto funciona porque as colunas datetime são armazenadas como valores binários de 8 bytes. Lançá-los para flutuar, pisá-los para remover a fração, e a porção de tempo dos valores se foram quando você lançá-los de volta para datetime. Está tudo a mudar sem lógica complicada e é muito rápido.
Tenha em atenção que isto depende de si. em um detalhe de implementação a Microsoft é livre de mudar a qualquer momento, mesmo em uma atualização automática de serviço. Também não é muito portátil. Na prática, é muito improvável que esta implementação mude a qualquer momento em breve, mas ainda é importante estar ciente do perigo se você optar por usá-lo. E agora que temos a opção de escolher um encontro, raramente é necessário.O caminho errado:
cast(convert(char(11), getdate(), 113) as datetime)
A maneira errada funciona convertendo para uma cadeia, truncando a cadeia, e convertendo de volta para um datetime. Está errado, por duas razões: 1) pode não funcionar em todos os locais e 2) é a maneira mais lenta possível de fazer isso... e não apenas um pouco; é como uma ordem de magnitude ou duas mais lentas do que as outras opções.
Update isto tem recebido alguns votos ultimamente, e então eu quero adicionar a ele que desde que eu postei isso eu vi algumas evidências sólidas que o servidor Sql irá otimizar a diferença de desempenho entre o caminho "correto" e o caminho "rápido", o que significa que você deve agora favorecer o primeiro.
Em ambos os casos, você quer escrever suas consultas para evitar a necessidade de fazer isso em primeiro lugar. É muito raro que você faça este trabalho na base de dados.
Na maioria dos lugares, a base de dados já é o seu ponto de estrangulamento. É geralmente o servidor que é o mais caro para adicionar hardware para melhorias de desempenho e o mais difícil para obter essas adições direito (você tem que discos de equilíbrio com memória, por exemplo). É também o mais difícil de escalar para fora, tanto tecnicamente quanto do ponto de vista empresarial; é muito mais fácil tecnicamente adicionar um servidor web ou aplicação do que um servidor de banco de dados e mesmo que isso fosse falso você não paga $20.000+ por licença de servidor para IIS ou apache. O que estou a tentar dizer é que, sempre que possível, deves fazer este trabalho ao nível da aplicação. O tempo apenas você deve encontrar-se truncando um datetime no servidor Sql é quando você precisa se agrupar ao dia, e mesmo assim você provavelmente deve ter uma coluna extra configurada como uma coluna computada, mantida na hora de inserir/atualizar, ou mantida na lógica de Aplicação. Tira Este trabalho pesado da tua base de dados.Apenas para o servidor SQL 2008
CAST(@SomeDateTime AS Date)
Então atira - a de volta para a data, se quiseres
CAST(CAST(@SomeDateTime AS Date) As datetime)
Só para uma resposta mais completa, aqui está uma maneira de trabalhar para truncar a qualquer uma das partes da data para baixo e incluindo minutos (substituir GETDATE()
com a data para truncar).
Isto é diferente da resposta aceite na medida em que pode usar não só dd
(dias), mas qualquer uma das partes da data (Ver Aqui):
dateadd(minute, datediff(minute, 0, GETDATE()), 0)
Note que na expressão acima, o 0
é uma data constante no início de um ano (1900-01-01). Se você precisa truncar para partes menores, como para segundos ou milisegundos, você precisa tomar uma data constante que está mais perto da data a ser truncado para evitar um excesso.
dateadd(dd,0, datediff(dd,0, YOURDATE))
e.g.
dateadd(dd,0, datediff(dd,0, getDate()))
Em SQL 2005 a sua função trunc_ data pode ser escrita desta forma.
(1)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
CAST(FLOOR( CAST( @date AS FLOAT ) )AS DATETIME)
END
O primeiro método é muito mais limpo. Ele usa apenas 3 chamadas de método incluindo o elenco final() e não realiza nenhuma concatenação de cadeia, que é um plus automático. Além disso, não existem moldes de tipo enorme aqui. Se você pode imaginar que os selos de data / hora podem ser representados, então a conversão de datas para números e de volta para datas é um processo bastante fácil.
(2)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
SELECT CONVERT(varchar, @date,112)
END
Se você está preocupado com a implementação da microsoft de datetimes (2) ou (3) pode estar 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
Terceiro, O método mais descritivo. Isso requer quebrar a data em suas partes ano, mês e dia, juntando-os em formato "AAAA/mm/dd", em seguida, lançá-lo de volta para uma data. Este método envolve 7 chamadas de método incluindo o elenco final(), para não mencionar a concatenação de cordas.
CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)
Seleccione o elenco [piso(getDate () como float)] como datetime] Referência: http://microsoftmiles.blogspot.com/2006/11/remove-time-from-datetime-in-sql-server.html
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)
Então, se hoje fosse, então isto voltaria.
Para mudar o intervalo para, substitua 1440, 0 pelo número de intervalos num dia, por exemplo:
24hrs = 24.0 (for every hour)
24hrs / 0.5hrs = 48.0 (for every half hour)
24hrs / (1/60) = 1440.0 (for every minute)
(põe sempre um .0
no fim para implicitamente lançar para um carro alegórico.)
Para aqueles que se perguntam:
(3.0/86400000)
é para o meu cálculo, o SQL Server 2005 não parece ter sido lançado de FLOAT
para DATETIME
com precisão, por isso isto adiciona 3 milissegundos antes de o soçobrar.
Esta consulta deve dar-lhe um resultado equivalente a trunc(sysdate)
no oráculo.
SELECT *
FROM your_table
WHERE CONVERT(varchar(12), your_column_name, 101)
= CONVERT(varchar(12), GETDATE(), 101)
Espero que isto ajude!
Você também pode extrair a data using Substring
da variável datetime e o lançamento de volta à datetime irá ignorar a parte do tempo.
declare @SomeDate datetime = '2009-05-28 16:30:22'
SELECT cast(substring(convert(varchar(12),@SomeDate,111),0,12) as Datetime)
Além disso, você pode acessar partes da variável datetime e fundi-las para uma data truncada de construção, algo do género:
SELECT cast(DATENAME(year, @Somedate) + '-' +
Convert(varchar(2),DATEPART(month, @Somedate)) + '-' +
DATENAME(day, @Somedate)
as datetime)
Oráculo:
TRUNC(SYSDATE, 'MONTH')
Servidor SQL:
DATEADD(DAY, - DATEPART(DAY, DateField) + 1, DateField)
Pode ser usado da mesma forma para truncar minutos ou horas a partir de uma data.
Podias apenas fazer isto (SQL 2008):
Declarar @something date = getdate ()
select @SomeDate
2009-05-28
TRUNC (aDate, 'DD') truncará o min, o sec e o hrs
SRC: http://www.techonthenet.com/oracle/functions/trunc_date.php