Лучший подход для удаления части времени datetime в SQL Server

Question

Лучший подход для удаления части времени datetime в SQL Server

какой метод обеспечивает лучшую производительность при удалении части времени из поля datetime в SQL Server?

a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

или

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

второй метод отправляет еще несколько байтов в любом случае, но это может быть не так важно, как скорость преобразования.

оба также кажутся очень быстрыми, но может быть разница в скорости при работе с сотнями тысяч или более строк?

кроме того, возможно, что там есть ли еще лучшие методы, чтобы избавиться от временной части datetime в SQL?

2283 23

sql-server sql tsql datetime date

23 ответов:

Comments

Ничего не найдено.

gbn · Accepted Answer · 2018-09-19 22:26:52

строго, метод a является наименее ресурсоемким:
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
доказано, что менее интенсивный процессор для той же общей продолжительности миллион строк кем-то с слишком большим количеством времени на руках: самый эффективный способ в SQL Server, чтобы получить дату от даты+времени?

Я видел подобный тест в другом месте с аналогичными результатами тоже.

Я предпочитаю DATEADD / DATEDIFF, потому что:

varchar зависит от языка / dateformat проблемы
Пример: почему мое выражение случая недетерминировано?

float опирается на внутреннюю память

он распространяется на работу в первый день месяца, завтра и т. д., изменяя базу "0"

Edit, Oct 2011

для SQL Server 2008+, Вы можете привести к date. Или просто использовать date Так нет времени, чтобы удалить.

Edit, Jan 2012

пример того, как гибкий это:нужно рассчитать по округленной цифре времени или даты в sql server

Edit, Май 2012

Не используйте это в предложениях WHERE и т. п., не думая: добавление функции или приведения к столбцу делает недействительным использование индекса. Смотрите номер 2 здесь: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

теперь у этого есть пример более позднего оптимизатора SQL Server версии, управляющие приведением на сегодняшний день правильно, но вообще это будет плохая идея ...

Edit, Sep 2018, for datetime2
DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'

select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)

Anto Raja Prakash · Accepted Answer · 2016-01-09 08:49:53

в SQL Server 2008 можно использовать:
CONVERT(DATE, getdate(), 101)

Arjan Fraaij · Accepted Answer · 2016-01-06 15:05:14

конечно, это старый поток, но чтобы сделать его полным.

из SQL 2008 Вы можете использовать тип данных даты, так что вы можете просто сделать:
SELECT CONVERT(DATE,GETDATE())

Gary McGill · Accepted Answer · 2009-07-24 15:57:54

SELECT CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)

19

2009-07-24 15:57:54

Metaphor · Accepted Answer · 2014-04-04 01:54:13

в SQL Server 2008 существует тип данных DATE (также тип данных TIME).
CAST(GetDate() as DATE)
или
declare @Dt as DATE = GetDate()

Jeff Meatball Yang · Accepted Answer · 2017-05-23 15:34:50

вот еще один ответ, из другой дублирую вопрос:
SELECT CAST(CAST(getutcdate() - 0.50000004 AS int) AS datetime) 
этот метод магических чисел работает немного быстрее, чем метод DATEADD. (Похоже на ~10%)

процессорное время на несколько раундов миллиона записей:
DATEADD   MAGIC FLOAT
500       453
453       360
375       375
406       360
но обратите внимание, что эти цифры, возможно, не имеют значения, потому что они уже очень быстро. Если у меня не было наборов записей 100 000 или более, я даже не мог получить время процессора для чтения выше нуль.

учитывая тот факт, что DateAdd предназначен для этой цели и является более надежным, я бы сказал, использовать DateAdd.

Byju · Accepted Answer · 2013-07-03 16:55:00

SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)

5

2013-07-03 16:55:00

Anton Gogolev · Accepted Answer · 2009-07-24 15:55:14

время прокладки на вставках / обновлениях в первую очередь. Что касается преобразования "на лету", ничто не может превзойти пользовательскую функцию maintanability-wise:
select date_only(dd)
реализация date_only может быть все, что угодно - теперь он абстрагирован и вызывающий код намного чище.

Joel Coehoorn · Accepted Answer · 2017-05-23 15:26:32

см. Этот вопрос:
Как я могу усечь datetime в SQL Server?

Что бы ты ни делал,не используйте метод String. Это самый худший способ сделать это.

Carter Cole · Accepted Answer · 2010-07-09 19:06:45

уже ответил, но я брошу это там тоже... это suposedly также преформирует хорошо, но он работает, отбрасывая десятичное число (которое хранит время) из поплавка и возвращая только целую часть (которая является датой)
 CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)
второй раз я нашел это решение... Я схватил этот код

shantanu singh chauhan · Accepted Answer · 2011-11-18 16:10:25

CAST(round(cast(getdate()as real),0,1) AS datetime)
этот метод не использовать строковые функции. Date это в основном реальный тип данных с цифрами до десятичной дроби в день.

это, я думаю, будет быстрее, чем много.

user1920017 · Accepted Answer · 2012-12-20 23:20:31

для меня приведенный ниже код всегда является победителем:
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())));

broslav · Accepted Answer · 2014-01-22 16:01:46

остерегайтесь!

метод А) и б) не всегда есть же выход!
select DATEADD(dd, DATEDIFF(dd, 0, '2013-12-31 23:59:59.999'), 0)
выход: 2014-01-01 00:00:00.000
select cast(convert(char(11), '2013-12-31 23:59:59.999', 113) as datetime)
выход: 2013-12-31 00:00:00.000

(проверено на MS SQL Server 2005 и 2008 R2)

EDIT: согласно комментарию Адама, это не может произойти, если Вы читаете значение даты из таблицы, но это может произойти, если вы предоставите свое значение даты как литерал (пример: как параметр хранимой процедуры, вызываемой через ADO.NET).

Diego · Accepted Answer · 2014-02-10 16:11:33

выберите конвертировать(char (10), GetDate (), 126)

emehex · Accepted Answer · 2017-01-10 19:58:09

мне очень нравится:
[date] = CONVERT(VARCHAR(10), GETDATE(), 120)
The 120 формат кода принудит дату в стандарт ISO 8601:
'YYYY-MM-DD' or '2017-01-09'
супер простой в использовании в dplyr (R) и панды (Python)!

Aaron West · Accepted Answer · 2012-10-26 07:37:36

Я думаю, что вы имеете в виду cast(floor(cast(getdate()as float))as datetime)

реальный только 32-бит, и может потерять некоторую информацию

Это самый быстрый cast(cast(getdate()+x-0.5 as int)as datetime)

...хотя только около 10% быстрее(about 0.49 microseconds CPU vs. 0.58)

Это было рекомендовано, и занимает то же время в моем тесте только сейчас: DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

в SQL 2008 функция SQL CLR примерно в 5 раз быстрее, чем при использовании функции SQL, в 1,35 микросекунды против 6,5 микросекунд, что указывает на гораздо более низкий вызов функции накладные расходы для функции SQL CLR по сравнению с простым SQL UDF.

в SQL 2005 функция SQL CLR в 16 раз быстрее, в соответствии с моим тестированием, по сравнению с этой медленной функцией:
create function dateonly (  @dt datetime )
returns datetime
as
begin
return cast(floor(cast(@dt as float))as int)
end

Dr. Drew · Accepted Answer · 2014-03-24 10:52:41

как о select cast(cast my_datetime_field as date) as datetime)? Это приводит к той же дате, с временем, установленным в 00:00, но избегает любого преобразования в текст, а также избегает любого явного числового округления.

Jamie G · Accepted Answer · 2016-03-26 18:53:03

Я думаю, что если вы строго придерживаетесь TSQL что это самый быстрый способ сократить время:
 select convert(datetime,convert(int,convert(float,[Modified])))
я обнаружил, что этот метод усечения примерно на 5% быстрее, чем DateAdd метод. И это можно легко изменить, чтобы округлить до ближайшего дня следующим образом:
select convert(datetime,ROUND(convert(float,[Modified]),0))

Max Vargas · Accepted Answer · 2016-03-26 19:07:58

здесь я сделал функцию для удаления некоторых частей datetime для SQL Server. Использование:

первый param-это дата и время, которые будут удалены.

второй параметр-это тип char:

s: округляет до секунд; удаляет миллисекунды

m: округляет до минут; удаляет секунды и миллисекунды

h: округляет до часов; удаляет минуты, секунды и миллисекунды.

d: округляет до дней; удаляет часы, минуты, секунды и миллисекунды.

возвращает новый объект datetime

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

Alan · Accepted Answer · 2016-03-31 04:32:38

на всякий случай, если кто-то ищет здесь версию Sybase, так как несколько версий выше не работали
CAST(CONVERT(DATE,GETDATE(),103) AS DATETIME)
протестировано в I SQL v11 работает на адаптивном сервере 15.7

tjeuten · Accepted Answer · 2011-10-13 17:40:19

Если возможно, для особых вещей, подобных этому, мне нравится использовать функции CLR.

в этом случае:
[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime DateOnly(SqlDateTime input)
    {
        if (!input.IsNull)
        {
            SqlDateTime dt = new SqlDateTime(input.Value.Year, input.Value.Month, input.Value.Day, 0, 0, 0);

            return dt;
        }
        else
            return SqlDateTime.Null;
    }

CraigTP · Accepted Answer · 2013-10-30 16:48:29

Я, лично, почти всегда использовать пользовательские функции для этого, если речь идет о SQL Server 2005 (или более низкой версии), однако, следует отметить, что существуют определенные недостатки в использовании UDF, особенно если применять их к where предложения (см. ниже и комментарии к этому ответу для получения дополнительной информации). При использовании SQL Server 2008 (или выше) - см. ниже.

на самом деле, для большинства баз данных, которые я создаю, я добавляю эти UDF в самом начале, так как я знаю есть 99% шанс, что они мне понадобятся рано или поздно.

Я создаю один для " только дата "и" только время "(хотя" только дата " один, безусловно, наиболее часто используется из двух).

вот некоторые ссылки на различные даты, связанные с UDF:

основные функции SQL Server Date, Time и DateTime
Получить Только Функцию Даты

эта последняя ссылка показывает не менее 3 различных способов получения даты только часть поле DateTime и упоминает некоторые плюсы и минусы каждого подхода.

при использовании UDF следует отметить, что вы должны стараться избегать использования UDF как части предложения WHERE в запросе, поскольку это значительно затруднит выполнение запроса. Основная причина этого заключается в том, что использование UDF в предложении WHERE отображает это предложение как non-sargable, что означает, что SQL Server больше не может использовать индекс с этим предложением для повышения скорости выполнения запроса. С ссылаясь на мое собственное использование UDF, я часто буду использовать столбец "raw" date в предложении WHERE, но применяю UDF к выбранному столбцу. Таким образом, UDF применяется только к отфильтрованному результирующему набору, а не к каждой строке таблицы в составе фильтра.

конечно, абсолютная лучшие подход для этого должен использовать SQL Server 2008 (или выше) и выделить свои дата и время, так как SQL Server database engine является изначально предоставление отдельных компонентов даты и времени и может эффективно запрашивать их независимо без необходимости использования UDF или другого механизма для извлечения части даты или времени из составного типа datetime.

Jabu · Accepted Answer · 2013-12-19 14:33:49

Я хотел бы использовать:
CAST
(
CAST(YEAR(DATEFIELD) as varchar(4)) + '/' CAST(MM(DATEFIELD) as varchar(2)) + '/' CAST(DD(DATEFIELD) as varchar(2)) as datetime
) 
таким образом, эффективно создавая новое поле из поля даты у вас уже есть.