什么是 sql-server 中的 ISO_year [英] What is ISO_year in sql-server

问题描述

我目前正在创建我的第一个 Data_warehouse 使用 sql-server

I am currently working on creating my first Data_warehouse using sql-server

我有一个日期维度我想使用SSIS填充它它有一个名为ISO_year

I have a Date dimension i want to populate it using SSIS it has a field called ISO_year

谁能告诉我如何获得它?

can somebody tell me how do i get it ?

我试过这个查询::

select year(getdate()) -- 但我不认为这是 ISO_year 吗?

select year(getdate()) -- but i dont think is this ISO_year ?

我需要知道使用 ssis 将维度加载到数据库中的最佳实践是什么?

And i need to know which is best practice to load dimensions into DB using ssis ?

我引用了这个 http://michaelmorley.name/how-to/create-date-dimension-table-in-sql-server

推荐答案

这是一个 Connect 项，它请求一个函数来计算 ISO_YEAR.

Here is a Connect item that requests a function to calculate ISO_YEAR.

DATEPART - ISO_YEAR for ISO_WEEK

在解决方法部分，您可以使用此功能.

In the workaround section you have this function that you can use.

CREATE FUNCTION [dbo].[ISOyear](@date DATETIME)
returns SMALLINT
AS
BEGIN
     DECLARE @isoyear SMALLINT = CASE
         WHEN Datepart(isowk, @date)=1
             AND Month(@date)=12 THEN Year(@date)+1
         WHEN Datepart(isowk, @date)=53
             AND Month(@date)=1 THEN Year(@date)-1
         WHEN Datepart(isowk, @date)=52
             AND Month(@date)=1 THEN Year(@date)-1             
         ELSE Year(@date)
        END;
     RETURN @isoyear;
END;

这篇关于什么是 sql-server 中的 ISO_year的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！