Получить дату из недели ISO в SQL

Я создал функцию в SQL, чтобы возвращать правильную дату из недели ISO в формате вроде «15W53». Где первая часть перед буквой «W» - это номер года, а вторая половина - это номер недели. Возвращенная дата должна возвращать начало недели для этой даты.

Так, например, 15W53 должен вернуть 12-28-2015, а 16W01 должен вернуть 01-04-2016. Однако, если я использую это для следующих примеров, я получаю неверные результаты из того, что я читал о неделе ISO.

Я неправильно создал функцию для разбора даты?

SET DATEFIRST 1; 
SELECT dbo.[GetDateFromISOweek]('15W52') AS Correct, '15W52'  -- Returns: 2015-12-21
SELECT dbo.[GetDateFromISOweek]('15W53') AS Correct, '15W53'  -- Returns: 2015-12-28
SELECT dbo.[GetDateFromISOweek]('16W01') AS Incorrect, '16W01'-- Returns: 2015-12-28
SELECT dbo.[GetDateFromISOweek]('16W02') AS Incorrect, '16W02'-- Returns: 2016-01-04
SELECT dbo.[GetDateFromISOweek]('16W03') AS Incorrect, '16W03'-- Returns: 2016-01-11

Функция:

CREATE FUNCTION [dbo].[GetDateFromISOweek] (@Input VARCHAR(10))  
RETURNS DATETIME  
WITH EXECUTE AS CALLER  
AS  
BEGIN  
    DECLARE @YearNum CHAR(4) 
    DECLARE @WeekNum VARCHAR(2)

    SET @YearNum = SUBSTRING(@Input,0,CHARINDEX('W',@Input,0))
    SET @WeekNum = SUBSTRING(@Input,CHARINDEX('W',@Input,0)+1,LEN(@Input))

    RETURN(DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 7));
END;

sql iso date-parsing
person InvaderZim    schedule 15.09.2017    source источник
comment
Я бы рекомендовал, чтобы каждый раз, когда вам нужно было написать функцию для вычисления даты, вам, вероятно, следует изучить создание таблицы Date Dimension. Сделает вашу жизнь на 1000% проще. mssqltips.com / sqlservertip / 4054 /   -  person Shawn    schedule 16.09.2017
comment
Привет, Шон, это отличная идея! Спасибо за ссылку!   -  person InvaderZim    schedule 17.09.2017

Ответы (1)


arrow_upward
2
arrow_downward

Измените -1 в своем отчете, чтобы рассчитать, следует ли считать первую неделю года первой или нет (если у вас более 3 дней). Что-то вроде этого

case when DATEDIFF ( day ,  convert(datetime,'01/01/'+ @YearNum),@FirstDay )>=3 then 1 else 0 end

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

CREATE FUNCTION [dbo].[GetDateFromISOweek] (@Input VARCHAR(10))  
RETURNS DATETIME  
WITH EXECUTE AS CALLER  
AS  
BEGIN  
    DECLARE @YearNum CHAR(4) 
    DECLARE @WeekNum VARCHAR(2)
    declare @FirstDay datetime

    SET @YearNum = cast(SUBSTRING(@Input,0,CHARINDEX('W',@Input,0)) as int)+2000
    SET @WeekNum = SUBSTRING(@Input,CHARINDEX('W',@Input,0)+1,LEN(@Input))
    set @FirstDay=DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, DATEADD(YEAR, @YearNum - 1900, 0)) +  (8 - @@DATEFIRST) * 2) % 7, DATEADD(YEAR, @YearNum - 1900, 0))-1

    RETURN(DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-case when DATEDIFF ( day ,  convert(datetime,'01/01/'+ @YearNum),@FirstDay )>=3 then 1 else 0 end), 7));
END; 
go
SET DATEFIRST 1; 
SELECT dbo.[GetDateFromISOweek]('15W52'),'15W52' union
SELECT dbo.[GetDateFromISOweek]('15W53'),'15W53' union
SELECT dbo.[GetDateFromISOweek]('16W01'), '16W01' union
SELECT dbo.[GetDateFromISOweek]('16W02'), '16W02' union
SELECT dbo.[GetDateFromISOweek]('16W03'), '16W03'

Результат будет

----------------------- -----
2015-12-21 00:00:00.000 15W52
2015-12-28 00:00:00.000 15W53
2016-01-04 00:00:00.000 16W01
2016-01-11 00:00:00.000 16W02
2016-01-18 00:00:00.000 16W03
person Fabricio    schedule 15.09.2017
comment
Ошибка ввода 18W01. Этот возврат 2018-01-08, ожидаемый результат 2018-01-01 - person DanB; 12.03.2019