Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 поведение DATEDIFF  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Господа,
прошу заценить следующее поведение DATEDIFF


WITH D AS 
( 
	SELECT CAST('20150109' AS DATE) AS DT1
), 
NN AS 
(
	SELECT N FROM 
	(VALUES 
		(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
		(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)
	) T(N)
) 

SELECT 
	DT1, 
	N, 
	DT2 = DATEADD(day, N, DT1),
	Weeks_diff = DATEDIFF(week, DT1, DATEADD(day, N, DT1))
FROM D, NN
ORDER BY N


Как я понял, datediff просто вычисляет номер недели у двух дат и берет между ними разницу. В результате мы получаем в данном примере, что уже через 2 дня даты начинают отличаться на неделю. И это зависит от первоначальной даты. Можно получить

0 0 1 1 1 .... можно 0 0 0 0 1 1 1 1 .... и др.

Мне нужно чтобы первые 6 дней отличались на 0 недель, с 7 по 13 отличались на 1 неделю и так далее.

Есть идеи как это легко получить?

Вопрос также касается месяцев и годов.

1 марта от 2 февраля должно отличаться на 0 месяцев, 1 марта от 1 февраля на 1 месяц.

1 января 2015 должно отличаться от 31 декабря 2014 на 0 лет, от 2 января 2014 на 0 лет, а от 1 января 2014 на 1 год. А не как тут

WITH D AS 
( 
	SELECT CAST('20141231' AS DATE) AS DT1
), 
NN AS 
(
	SELECT N FROM 
	(VALUES 
		(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
		(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)
	) T(N)
) 

SELECT 
	DT1, 
	N, 
	DT2 = DATEADD(day, N, DT1),
	Weeks_year = DATEDIFF(year, DT1, DATEADD(day, N, DT1))
FROM D, NN
ORDER BY N


К сообщению приложен файл. Размер - 13Kb
2 июл 15, 12:27    [17842630]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
Glory
Member

Откуда:
Сообщений: 104760
a_voronin
Мне нужно чтобы первые 6 дней отличались на 0 недель, с 7 по 13 отличались на 1 неделю и так далее.

Т.е. вы хотите, чтобы сервер вычислял номер недели по вашему алгоритму что ли ?
2 июл 15, 12:32    [17842659]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
a_voronin, ну почитайте help, что ли...

https://msdn.microsoft.com/ru-ru/library/ms189794.aspx

Если вас не устраивает алгоритм вычисления разницы - напишите свой.

Зачем же выдвигать собственные требования к стандартным процедурам с описанным поведением?
2 июл 15, 12:35    [17842681]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
0-0
Guest
a_voronin
Как я понял, datediff просто вычисляет номер недели у двух дат и берет между ними разницу. В результате мы получаем в данном примере, что уже через 2 дня даты начинают отличаться на неделю.


У вас стоит американский формат, поэтому воскресенье - первый день недели.
2015-01-09 - пятница (текущая неделя)
2015-01-11 - воскресенье (т.е. уже следующая неделя)

Разница в неделях между ними 1. Так работает datediff. А вам как бы хотелось?
2 июл 15, 12:37    [17842694]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
a_voronin
Как я понял, datediff просто вычисляет номер недели у двух дат и берет между ними разницу
Нет. Это не так. Здесь мы имеем дело с загадочным глюком MSSQL.
DATEDIFF(WEEK,D1,D2) возвращает количество пар рядом стоящих "суббота-воскресенье", которые попали между D1 и D2.
Этот результат ни от чего не зависит!
2 июл 15, 12:37    [17842695]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
0-0
У вас стоит американский формат, поэтому воскресенье - первый день недели
Не имеет значения для DATEDIFF(WEEK,...)
2 июл 15, 12:38    [17842703]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Minamoto
a_voronin, ну почитайте help, что ли...

https://msdn.microsoft.com/ru-ru/library/ms189794.aspx

Если вас не устраивает алгоритм вычисления разницы - напишите свой.

Зачем же выдвигать собственные требования к стандартным процедурам с описанным поведением?


Хотелось бы задействовать существующий функционал -- например, чтобы учитывалось количество дней в месяце при разнице по месяцам.
2 июл 15, 12:52    [17842790]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
Кролик-зануда
Guest
a_voronin,

?
+ осторожно, отсутствует iif

WITH D AS 
( 
	SELECT CAST('20000209' AS DATE) AS DT1
), 
NN AS 
(
	SELECT N FROM 
	(VALUES 
		(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
		(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
		(28),(29),(30),(31),(364),(365),(366)
	) T(N)
) 
SELECT 
	DT1, 
	N, 
	DT2,
	Weeks_diff  = DATEDIFF(day, DT1, dt2)/7,
	Months_diff = DATEDIFF(month, DT1, dt2)-case when datepart(day,dt1)>datepart(day,dt2) then 1 else 0 end,
	years_diff  = DATEDIFF(year, DT1, dt2)-case when right(convert(char(8),dt1,112),4)>right(convert(char(8),dt2,112),4)then 1 else 0 end
FROM D
cross join NN
cross apply(select DATEADD(day, N, DT1) dt2)dt2
ORDER BY N

2 июл 15, 12:54    [17842803]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
iap
0-0
У вас стоит американский формат, поэтому воскресенье - первый день недели
Не имеет значения для DATEDIFF(WEEK,...)


Посмотрите также как она ведет себя по отношению к годам и месяцам
2 июл 15, 12:54    [17842805]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Glory
a_voronin
Мне нужно чтобы первые 6 дней отличались на 0 недель, с 7 по 13 отличались на 1 неделю и так далее.

Т.е. вы хотите, чтобы сервер вычислял номер недели по вашему алгоритму что ли ?


Такое желание появилось не у меня, а у заказчика. Хочет, чтобы в "до недели назад" попадало 7 дней включая GETDATE(). А в 1 неделю назад следующие 7 дней и т.д. Аналогично месяц и год.
2 июл 15, 12:56    [17842821]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
Glory
Member

Откуда:
Сообщений: 104760
a_voronin
Такое желание появилось не у меня, а у заказчика.

Да хоть у соседа сверху/снизу.

Я думаю, что inmemory, view-календарь и SSIS вам помогут написать свой функционал вычисления номеров недель и разниц между датами
2 июл 15, 12:58    [17842831]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
msLex
Member

Откуда:
Сообщений: 8193
a_voronin
Glory
пропущено...

Т.е. вы хотите, чтобы сервер вычислял номер недели по вашему алгоритму что ли ?


Такое желание появилось не у меня, а у заказчика. Хочет, чтобы в "до недели назад" попадало 7 дней включая GETDATE(). А в 1 неделю назад следующие 7 дней и т.д.

datediff(dd,...) / 7


автор
Аналогично месяц и год.

Аналогично это как?
С неделей понятно, это 7 дней. А сколько дней в месяце и годе?
2 июл 15, 12:58    [17842834]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
a_voronin
iap
пропущено...
Не имеет значения для DATEDIFF(WEEK,...)


Посмотрите также как она ведет себя по отношению к годам и месяцам
И как же?
Разница в неделях очевидно ДОЛЖНА зависеть от первого дня недели. Но не зависит.
А с разницей в месяцах и годах что? У всех вроде бы месяцы и годы начинаются одновременно.
2 июл 15, 12:59    [17842838]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
iap
a_voronin
пропущено...


Посмотрите также как она ведет себя по отношению к годам и месяцам
И как же?
Разница в неделях очевидно ДОЛЖНА зависеть от первого дня недели. Но не зависит.
А с разницей в месяцах и годах что? У всех вроде бы месяцы и годы начинаются одновременно.


31 декабря от 1 января отличаются на 1 год. Второй скрипт 17842630
2 июл 15, 13:02    [17842873]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4262
a_voronin
Такое желание появилось не у меня, а у заказчика. Хочет, чтобы в "до недели назад" попадало 7 дней включая GETDATE(). А в 1 неделю назад следующие 7 дней и т.д. Аналогично месяц и год.

Тогда не стоит закладываться на номер недели в году, а оперировать днями, группируя их в недели пачками по 7 штук.
Кто-то уже предлагал подобное в этом топике.
2 июл 15, 13:03    [17842883]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Кролик-зануда
a_voronin,


WITH D AS 
( 
	SELECT CAST('20000209' AS DATE) AS DT1
), 
NN AS 
(
	SELECT N FROM 
	(VALUES 
		(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
		(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
		(28),(29),(30),(31),(364),(365),(366)
	) T(N)
) 
SELECT 
	DT1, 
	N, 
	DT2,
	Weeks_diff  = DATEDIFF(day, DT1, dt2)/7,
	Months_diff = DATEDIFF(month, DT1, dt2)-case when datepart(day,dt1)>datepart(day,dt2) then 1 else 0 end,
	years_diff  = DATEDIFF(year, DT1, dt2)-case when right(convert(char(8),dt1,112),4)>right(convert(char(8),dt2,112),4)then 1 else 0 end
FROM D
cross join NN
cross apply(select DATEADD(day, N, DT1) dt2)dt2
ORDER BY N



Вот это круто. Единственно оно не собьется на 28 февраля и високосных годах?
2 июл 15, 13:03    [17842884]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
a_voronin
iap
пропущено...
И как же?
Разница в неделях очевидно ДОЛЖНА зависеть от первого дня недели. Но не зависит.
А с разницей в месяцах и годах что? У всех вроде бы месяцы и годы начинаются одновременно.


31 декабря от 1 января отличаются на 1 год. Второй скрипт 17842630
И это правильно! Речь ведь идёт о номере года, и только.
О DATEPART(YEAR, [Date]), если сформулировать иначе.
Такова специфика DATEDIFF
2 июл 15, 13:04    [17842901]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
Glory
Member

Откуда:
Сообщений: 104760
a_voronin
31 декабря от 1 января отличаются на 1 год.

Потому что DATEDIFF возвращает не число лет, а
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
2 июл 15, 13:05    [17842902]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Glory
a_voronin
31 декабря от 1 января отличаются на 1 год.

Потому что DATEDIFF возвращает не число лет, а
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.


Что вполне соответствует тому, что я сказал в начале поста

"Как я понял, datediff просто вычисляет номер недели у двух дат и берет между ними разницу. "
2 июл 15, 13:09    [17842947]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
Glory
Member

Откуда:
Сообщений: 104760
a_voronin
Что вполне соответствует тому, что я сказал в начале поста

"Как я понял, datediff просто вычисляет номер недели у двух дат и берет между ними разницу. "

Нет.
Количество пересечений границ - это не разница номеров недель
2 июл 15, 13:13    [17842989]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
Кролик-зануда
Guest
a_voronin,
автор
Единственно оно не собьется на 28 февраля и високосных годах?

а проверить? :)
я же даже вбил туда високосный год, осталось только нужные N подставить ;)
2 июл 15, 13:19    [17843035]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
a_voronin,

номер недели зависит от SET DATEFIRST:
SET DATEFIRST 4;
SELECT DATEPART(WEEK,'20150206');
SET DATEFIRST 5;
SELECT DATEPART(WEEK,'20150206');
Разница, стало быть, тоже.
Однако, DATEDIFF ни от чего не зависит:
SET DATEFIRST 4;
SELECT DATEPART(WEEK,'20150205');
SELECT DATEPART(WEEK,'20150206');
SELECT DATEPART(WEEK,'20150206')-DATEPART(WEEK,'20150205');
SELECT DATEDIFF(WEEK,'20150205','20150206');
SET DATEFIRST 5;
SELECT DATEPART(WEEK,'20150205');
SELECT DATEPART(WEEK,'20150206');
SELECT DATEPART(WEEK,'20150206')-DATEPART(WEEK,'20150205');
SELECT DATEDIFF(WEEK,'20150205','20150206');
2 июл 15, 13:19    [17843042]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
o-o
Guest
a_voronin
Glory
пропущено...

Потому что DATEDIFF возвращает не число лет, а
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.


Что вполне соответствует тому, что я сказал в начале поста

"Как я понял, datediff просто вычисляет номер недели у двух дат и берет между ними разницу. "

set language russian;

declare @dt1 datetime = '20150628',
        @dt2 datetime = '20150629';
select DATEDIFF(WW, @dt1, @dt1) as [DATEDIFF(WW, @dt1, @dt1)],
       datename(DW, @dt1) as day1,
       datename(DW, @dt2) as day2,
       DATEPART(WW, @dt1) as week1,
       DATEPART(WW, @dt2) as week2,
       DATEPART(WW, @dt2) - DATEPART(WW, @dt1) as [week2 - week1] 
----------------------------------------------------------------------
DATEDIFF(WW, @dt1, @dt1)	day1	day2	week1	week2	week2 - week1
0	воскресенье	понедельник	26	27	1
2 июл 15, 13:23    [17843080]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
0-0
Guest
iap
0-0
У вас стоит американский формат, поэтому воскресенье - первый день недели
Не имеет значения для DATEDIFF(WEEK,...)


Да, верно, попутал с datediff(dw,...)
Спасибо, что поправили
2 июл 15, 13:37    [17843215]     Ответить | Цитировать Сообщить модератору
 Re: поведение DATEDIFF  [new]
0-0
Guest
a_voronin
Такое желание появилось не у меня, а у заказчика. Хочет, чтобы в "до недели назад" попадало 7 дней включая GETDATE().


А в чем проблема то?
select dateadd(day, -7, cast(getdate() as date)) db, cast(getdate() as date) as de
2 июл 15, 13:51    [17843332]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить