日历表 - 月份的周数 [英] Calendar Table - Week number of month
本文介绍了日历表 - 月份的周数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个日历表,其中包含 2000 年到 2012 年的数据(2012 年不是故意的!).我只是意识到我没有月份的周数(例如,1 月 1、2、3、4 月 1、2、3、4)
I have a calendar table with data from year 2000 to 2012 (2012 wasn't intentional!). I just realize that I don't have the week number of month (e.g In January 1,2,3,4 February 1,2,3,4)
如何计算一个月中的周数来填充此表?
How do I go about calculating the week numbers in a month to fill this table?
这是表架构
CREATE TABLE [TCalendar] (
[TimeKey] [int] NOT NULL ,
[FullDateAlternateKey] [datetime] NOT NULL ,
[HolidayKey] [tinyint] NULL ,
[IsWeekDay] [tinyint] NULL ,
[DayNumberOfWeek] [tinyint] NULL ,
[EnglishDayNameOfWeek] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpanishDayNameOfWeek] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FrenchDayNameOfWeek] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DayNumberOfMonth] [tinyint] NULL ,
[DayNumberOfYear] [smallint] NULL ,
[WeekNumberOfYear] [tinyint] NULL ,
[EnglishMonthName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpanishMonthName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FrenchMonthName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MonthNumberOfYear] [tinyint] NULL ,
[CalendarQuarter] [tinyint] NULL ,
[CalendarYear] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CalendarSemester] [tinyint] NULL ,
[FiscalQuarter] [tinyint] NULL ,
[FiscalYear] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FiscalSemester] [tinyint] NULL ,
[IsLastDayInMonth] [tinyint] NULL ,
CONSTRAINT [PK_TCalendar] PRIMARY KEY CLUSTERED
(
[TimeKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
推荐答案
update TCalendar
set = WeekNumberOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, FullDateAlternateKey), 0), FullDateAlternateKey) +1
这篇关于日历表 - 月份的周数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文