我正在为我的仓库创建一个日历表。我将使用它作为所有日期字段的外键。
下面显示的代码创建并填充表。我能够找出如何找到阵亡将士纪念日(5月的最后一个星期一)和劳动节(9月的第一个星期一)。
SET NOCOUNT ON
DROP Table dbo.Calendar
GO
Create Table dbo.Calendar
(
CalendarId Integer NOT NULL,
DateValue Date NOT NULL,
DayNumberOfWeek Integer NOT NULL,
NameOfDay VarChar (10) NOT NULL,
NameOfMonth VarChar (10) NOT NULL,
WeekOfYear Integer NOT NULL,
JulianDay Integer NOT NULL,
USAIsBankHoliday Bit NOT NULL,
USADayName VarChar (100) NULL,
)
ALTER TABLE dbo.Calendar ADD CONSTRAINT
DF_Calendar_USAIsBankHoliday DEFAULT 0 FOR USAIsBankHoliday
GO
ALTER TABLE dbo.Calendar ADD CONSTRAINT
DF_Calendar_USADayName DEFAULT '' FOR USADayName
GO
Declare @StartDate DateTime = '01/01/2000'
Declare @EndDate DateTime = '01/01/2020'
While @StartDate < @EndDate
Begin
INSERT INTO dbo.Calendar
(
CalendarId,
DateValue,
WeekOfYear,
DayNumberOfWeek,
NameOfDay,
NameOfMonth,
JulianDay
)
Values
(
YEAR (@StartDate) * 10000 + MONTH (@StartDate) * 100 + Day (@StartDate), --CalendarId
@StartDate, -- DateValue
DATEPART (ww, @StartDate), -- WeekOfYear
DATEPART (dw, @StartDate), -- DayNumberOfWeek
DATENAME (dw, @StartDate), -- NameOfDay
DATENAME (M, @StartDate), -- NameOfMonth
DATEPART (dy, @StartDate) -- JulianDay
)
Set @StartDate += 1
End
--=========================== Weekends
-- saturday and sunday
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Weekend, ' WHERE DayNumberOfWeek IN (1, 7)
--=========================== Bank Holidays
-- new years day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'New Year''s Day, ' WHERE (CalendarId % 2000) IN (101)
-- memorial day (last Monday in May)
UPDATE dbo.Calendar
SET USAIsBankHoliday = 1,
USADayName += 'Memorial Day, '
WHERE 1=1
AND CalendarId IN
(
SELECT MAX (CalendarId)
FROM dbo.Calendar
WHERE MONTH (DateValue) = 5
AND DATEPART (DW, DateValue)=2
GROUP BY YEAR (datevalue)
)
-- independence day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Independence Day, ' WHERE (CalendarId % 2000) IN (704)
-- labor day (first Monday in September)
UPDATE dbo.Calendar
SET USAIsBankHoliday = 1,
USADayName += 'Labor Day, '
WHERE 1=1
AND CalendarId IN
(
SELECT MIN (CalendarId)
FROM dbo.Calendar
WHERE MONTH (DateValue) = 9
AND DATEPART (DW, DateValue)=2
GROUP BY YEAR (datevalue)
)
-- thanksgiving day (fourth Thursday in November)
UPDATE dbo.Calendar
SET USAIsBankHoliday = 1,
USADayName += 'Thanksgiving Day, '
WHERE 1=1
AND CalendarId IN
(
SELECT Max (CalendarId)
FROM dbo.Calendar
WHERE MONTH (DateValue) = 11
AND DATEPART (DW, DateValue)=5
GROUP BY YEAR (datevalue)
)
-- christmas
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Christmas Day, ' WHERE (CalendarId % 2000) IN (1225)
--=========================== Other named days
-- new years eve
UPDATE dbo.Calendar SET USADayName += 'New Year''s Eve, ' WHERE (CalendarId % 2000) IN (1231)
-- christmas eve
UPDATE dbo.Calendar SET USADayName += 'Christmas Eve, ' WHERE (CalendarId % 2000) IN (1224)
-- boxing day
UPDATE dbo.Calendar SET USADayName += 'Boxing Day, ' WHERE (CalendarId % 2000) IN (1226)
--=========================== Remove trailing comma
UPDATE dbo.Calendar SET USADayName = SubString (USADayName, 1, LEN (USADayName) -1) WHERE LEN (USADayName) > 2
SELECT * FROM dbo.Calendar
我被弄清楚感恩节(11月最后一个完整周的星期四)难住了。
编辑:根据John Sauer的评论进行更正
感恩节是11月的第四个星期四,然而,经过几年的检查,我发现它也是11月最后一个完整星期的星期四。
5条答案
按热度按时间0x6upsns1#
我被弄清楚感恩节(11月最后一个完整周的星期四)难住了。
11月2日最后一个星期六
e3bfsja22#
取11月的最后一个星期六,减去两天;)
9w11ddsr3#
对于复杂的算法,有时从一个集合中找到一个匹配的日期比试图构造一个庞大的单值公式要好。
请参阅我的博客中的这篇文章以了解更多详细信息:
t5fffqht4#
日期是11月的星期四吗?还有不到一周的时间吗?
lsmd5eda5#
我们在数据库中使用了一个简洁的标量值函数,可以调用该函数来确定某个日期是否为NonWorkDay。
创建函数:
调用函数: