如何获得月份和月份的周数？ [英] How to get the number of week in month amd month ?
本文介绍了如何获得月份和月份的周数？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
您好
我希望从数据库中获取一段时间的数据并将其显示在图表上。我需要根据他们的分组进行分组。例如：
1月1日，2月1日，1月1日，1月1日，1月2日，2月2日。
我怎样才能实现这一目标？ br />
我尝试过的事情：
分组依据（c = > SqlFunctions.DatePart（ 周，c.MyDate））;
// 连续显示周数而不显示月数
先谢谢
解决方案
参见根据ISO8601的周数 [ ^ ]，显示如何查找一年中的特定开始日期。从那以后你应该能够推断出你想要的日期。
这是承诺的代码。在我的数据库服务器上，此代码位于Reference_Tables数据库中。更改USE语句以反映您自己的范例。
首先，是IsHoliday标量函数。如果你不需要联邦假期，你可以跳过这个，但是如果你确实需要这个，你必须在添加下面的存储过程之前添加这个功能*
USE [Reference_Tables]
GO
/ * *****对象：UserDefinedFunction [dbo]。[fn_IsHoliday]脚本日期：01/18/2017 10 ：39：07 ****** /
SET ANSI_NULLS < span class =code-keyword> ON
GO
SET QUOTED_IDENTIFIER ON
GO
- 如果指定的日期是联邦，则此函数返回位值1
- 假日，如果不是假日，则为0。
创建 < span class =code-keyword> FUNCTION [dbo]。[fn_IsHoliday]
（
@ date date
）
RETURNS 位</ span>
AS
BEGIN
DECLARE @ year int = DATEPART（年， @日期）;
DECLARE @ month int = DATEPART（MONTH， @ date ）;
DECLARE @ day int = DATEPART（DAY， @ date ）;
DECLARE @ dayName varchar （ 12 ）= DATENAME（DW， @ date ）;
DECLARE @ nthWeekDay int = ceiling（ @ day / 7 。 0 ）;
DECLARE @ isThursday 位</ span> = CASE WHEN @ dayName LIKE ' Thursday' 那么 1 ELSE 0 END ;
DECLARE @ isFriday 位</ span> = CASE WHEN @ dayName LIKE ' Friday' 那么 1 ELSE 0 END ;
DECLARE @ isSaturday 位</ span> = CASE WHEN @ dayName LIKE ' 星期六' 那么 1 ELSE 0 END ;
DECLARE @ isSunday 位</ span> = CASE WHEN @ dayName LIKE ' 星期日' 那么 1 ELSE 0 END ;
DECLARE @ isMonday 位</ span> = CASE WHEN @ dayName LIKE ' 星期一' 那么 1 ELSE 0 END ;
DECLARE @ isWeekend 位</ span> = CASE WHEN @ isSaturday = 1 OR @ isSunday = 1 那么 1 ELSE 0 END ;
- - 元旦
if （ @ month = 12 AND @ day = 31 AND @ isFriday = 1） return 1 ;
if （ @ month = 1 AND @ day = 1 < span class =code-keyword> AND @ isWeekend = 0） return 1 ;
if （ @ month = 1 AND @ day = 2 < span class =code-keyword> AND @ isMonday = 1） return 1 ;
- - MLK日
if （ @ month = 1 AND @ isMonday = 1 AND @ nthWeekDay = 3 ）返回 1 ;
- ----总统日（2月的第3个星期一）
if （ @ month = 2 AND @ isMonday = 1 < span class =code-keyword> AND @ nthWeekDay = 3 ）返回 1 ;
- ----阵亡将士纪念日（5月上周一）
if （ @ month = 5 AND @ isMonday = 1 < span class =code-keyword> AND DATEPART（MONTH，DATEADD（DAY， 7 ， @ Date ））= 6 ） return 1 < /跨度>;
- ----独立日（7月4日）
if （ @ month = 7 AND @ day = 3 AND @ isFriday = 1 ） return 1 ;
if （ @ month = 7 AND @ day = 4 < span class =code-keyword> AND @ isWeekend = 0 ）返回 1 ;
if （ @ month = 7 AND @ day = 5 < span class =code-keyword> AND @ isMonday = 1 ）返回 1 ;
- ----劳动节（9月1日星期一）
if （ @ month = 9 AND @ isMonday = 1 < span class =code-keyword> AND @ nthWeekDay = 1 ）返回 1 ;
- ----哥伦布日（10月的第二个星期一）
if （ @ month = 10 AND @ isMonday = 1 < span class =code-keyword> AND @ nthWeekDay = 2 ）返回 1 ;
- ----退伍军人节（11月11日）
if （ @ month = 11 AND @ day = 10 AND @ isFriday = 1 ） return 1 ;
if （ @ month = 11 AND @ day = 11 < span class =code-keyword> AND @ isWeekend = 0 ）返回 1 ;
if （ @ month = 11 AND @ day = 12 < span class =code-keyword> AND @ isMonday = 1 ）返回 1 ;
- ----感恩节（11月的第4个星期四）
if （ @ month = 11 AND @ isThursday = 1 < span class =code-keyword> AND @ nthWeekDay = 4 ）返回 1 ;
- ----圣诞节（12月25日）
if （ @ month = 12 AND @ day = 24 AND @ isFriday = 1 ） return 1 ;
if （ @ month = 12 AND @ day = 25 < span class =code-keyword> AND @ isWeekend = 0 ）返回 1 ;
if （ @ month = 12 AND @ day = 25 < span class =code-keyword> AND @ isMonday = 1 ）返回 1 ;
return 0 ;
END
GO< br $>
此存储过程根据指定的日期范围返回日期表（如果需要，日期范围可以是一天）。
USE [Reference_Tables]
GO
/ * *****对象：StoredProcedure [dbo]。[sp_GetCalendar]脚本日期：01/18/2017 10:38:19 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
创建 程序 [dbo]。[sp_GetCalendar]
@ startDate DATE ， - 日期范围的开头
@ endDate DATE - 日期范围的结尾
AS
BEGIN
SET NOCOUNT ON ;
- 完整性检查 - 如果开始日期不早于或等于
- 结束日期，切换它们
if （ @ startDate > @ endDate ）
BEGIN
DECLARE @ temp date = @ startDate ;
SET @ startDate = @ endDate ;
SET @ endDate = @ temp ;
END
; WITH n AS
（
SELECT TOP （DATEDIFF） （DAY， @ startDate ， @ endDate ）+ 1 ）n = ROW_NUMBER（） OVER （ ORDER BY [object_id]）
FROM sys.all_objects
）
SELECT DATEADD（DAY，n-1， @ startDate ） AS CalendarDate
，DATEPART（年份，DATEADD（DAY，n-1， @ startDate ）） AS CalendarYear
，DATEPART（月，DATEADD（DAY，n-1， @ startDate ）） AS CalendarMonth
，DATEPART（QUARTER，DATEADD（DAY，n-1，< span class =code-sdkkeyword> @ startDate ）） AS CalendarQuarter
，DATEPART（DAYOFYEAR，DATEADD（DAY，n-1， @ startDate ））作为 CalendarJulianDay
，DATEADD（MONTH， 3 ，DATEADD（DAY，n-1， @ startDate ）） AS FiscalDate
，DATEPART（年份，DATEADD（月， 3 ，DATEADD（DAY，n-1， @ startDate ））） AS FiscalYear
，DATEPART（MONTH，DATEADD（MONTH， 3 ，DATEADD（DAY，n-1， @ startDate ））） AS FiscalMonth
，DATEPART（QUARTER，DATEADD（月， 3 ，DATEADD（DAY，n-1， @ startDate ） ）） AS FiscalQuarter
，DATEPART（DAYOFYEAR，DATEADD（月， 3 ，DATEADD（DAY） ，n-1， @ startDate ）））作为 FiscalJulianDay
，DATENAME（MONTH，DATEADD） （DAY，n-1， @ startDate ）） AS [MonthName]
，DATEPART（ DAY，DATEADD（DAY，n-1， @ startDate ）） AS [DayOfMonth]
，DATEPART（WEEKDAY，DATEADD（DAY，n-1， @ startDate ）） AS WeekDayNumber
，DATENAME（WEEKDAY，DATEADD（DAY，n-1， @ startDate ）） AS W eekDayName
，ceiling（DATEPART（DAY，DATEADD（DAY，n-1， @ startDate ））/ 7 。 0 ）作为 NthWeekday
- 如果您不需要IsHoliday函数，只需注释掉
- 以下行
，dbo.fn_IsHoliday（DATEADD（DAY，n-1， @ startDate ）） AS IsHoliday
FROM n;
END
GO
要获得日期所在的第n周（一个月内），您可以使用返回的NthWeekdaycolumn。
多么巧合 - 我刚刚写了一些基于指定日期范围创建日历的SQL代码。它只不过是您在数据库中实现的存储过程和函数。只需调用存储过程，它将返回日历，其中包括日历年，月，日，朱利安日和季度，会计年度，月份，季度和朱利安日，以及第n个工作日，第n周，以及日期是否为联邦假日（假设是周末假期）。
刺激这种发展的商业案例是，分析师使用的表格数据没有超过2016年12月31日，而且必须每年手动更新。我的方法不需要表格，并且可以返回任何日期范围的日历信息。
当我星期一回到工作岗位时，我会在这里发布。
Hello
I want to get data from data base for a period of time and show it on charts. I need to group by them, based on the week. For example :
1st-Jan, 2nd-Jan, 3rd-Jan, 4th-Jan, 1st-Feb, 2nd-Feb.
How can I achieve that ?
What I have tried:
Group By (c => SqlFunctions.DatePart("week", c.MyDate));
// Show just the number of week continuously without showing months
Thanks in Advance
解决方案
See Week numbers according to ISO8601[^] which shows how to find a specific beginning day in the year. From that you should be able to extrapolate the dates you are looking for.
Here's the promised code. On my db server, this code lives in aReference_Tablesdatabase. Change theUSEstatement to reflect your own paradigm.
First, is the IsHoliday scalar function. If you don't need federal holidays, you can skip this, but if you do need this, you must add this function *before* adding the stored proc below.
USE [Reference_Tables] GO /****** Object: UserDefinedFunction [dbo].[fn_IsHoliday] Script Date: 01/18/2017 10:39:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- This function returns a bit value of 1 if the specified date is a federal -- holiday, or a 0 if it isn't a holiday. CREATE FUNCTION [dbo].[fn_IsHoliday] ( @date date ) RETURNS bit AS BEGIN DECLARE @year int = DATEPART(YEAR, @date); DECLARE @month int = DATEPART(MONTH,@date); DECLARE @day int = DATEPART(DAY, @date); DECLARE @dayName varchar(12) = DATENAME(DW, @date ); DECLARE @nthWeekDay int = ceiling(@day / 7.0); DECLARE @isThursday bit = CASE WHEN @dayName LIKE 'Thursday' THEN 1 ELSE 0 END; DECLARE @isFriday bit = CASE WHEN @dayName LIKE 'Friday' THEN 1 ELSE 0 END; DECLARE @isSaturday bit = CASE WHEN @dayName LIKE 'Saturday' THEN 1 ELSE 0 END; DECLARE @isSunday bit = CASE WHEN @dayName LIKE 'Sunday' THEN 1 ELSE 0 END; DECLARE @isMonday bit = CASE WHEN @dayName LIKE 'Monday' THEN 1 ELSE 0 END; DECLARE @isWeekend bit = CASE WHEN @isSaturday = 1 OR @isSunday = 1 THEN 1 ELSE 0 END; ---- New Years Day if (@month = 12 AND @day = 31 AND @isFriday=1) return 1; if (@month = 1 AND @day = 1 AND @isWeekend=0) return 1; if (@month = 1 AND @day = 2 AND @isMonday=1) return 1; ---- MLK day if (@month = 1 AND @isMonday = 1 AND @nthWeekDay = 3) return 1; ------ President’s Day ( 3rd Monday in February ) if (@month = 2 AND @isMonday = 1 AND @nthWeekDay = 3) return 1; ------ Memorial Day ( Last Monday in May ) if (@month = 5 AND @isMonday = 1 AND DATEPART(MONTH, DATEADD(DAY, 7, @Date)) = 6) return 1; ------ Independence Day ( July 4 ) if (@month = 7 AND @day = 3 AND @isFriday = 1) return 1; if (@month = 7 AND @day = 4 AND @isWeekend = 0) return 1; if (@month = 7 AND @day = 5 AND @isMonday = 1) return 1; ------ Labor Day ( 1st Monday in September ) if (@month = 9 AND @isMonday = 1 AND @nthWeekDay = 1) return 1; ------ Columbus Day ( 2nd Monday in October ) if (@month = 10 AND @isMonday = 1 AND @nthWeekDay = 2) return 1; ------ Veteran’s Day ( November 11 ) if (@month = 11 AND @day = 10 AND @isFriday = 1) return 1; if (@month = 11 AND @day = 11 AND @isWeekend = 0) return 1; if (@month = 11 AND @day = 12 AND @isMonday = 1) return 1; ------ Thanksgiving Day ( 4th Thursday in November ) if (@month = 11 AND @isThursday = 1 AND @nthWeekDay = 4) return 1; ------ Christmas Day ( December 25 ) if (@month = 12 AND @day = 24 AND @isFriday = 1) return 1; if (@month = 12 AND @day = 25 AND @isWeekend = 0) return 1; if (@month = 12 AND @day = 25 AND @isMonday = 1) return 1; return 0; END GO
This stored procedure returns a table of dates based on the specified date range (the date range can be a single day if desired).
USE [Reference_Tables] GO /****** Object: StoredProcedure [dbo].[sp_GetCalendar] Script Date: 01/18/2017 10:38:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_GetCalendar] @startDate DATE, -- the start of the date range @endDate DATE -- the end of the date range AS BEGIN SET NOCOUNT ON; -- sanity check - if the start date is not earlier than or equal to the -- end date, switch them around if (@startDate > @endDate) BEGIN DECLARE @temp date = @startDate; SET @startDate = @endDate; SET @endDate = @temp; END ;WITH n AS ( SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ) SELECT DATEADD(DAY, n-1, @startDate) AS CalendarDate ,DATEPART(YEAR, DATEADD(DAY, n-1, @startDate)) AS CalendarYear ,DATEPART(MONTH, DATEADD(DAY, n-1, @startDate)) AS CalendarMonth ,DATEPART(QUARTER, DATEADD(DAY, n-1, @startDate)) AS CalendarQuarter ,DATEPART(DAYOFYEAR, DATEADD(DAY, n-1, @startDate)) As CalendarJulianDay ,DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate)) AS FiscalDate ,DATEPART(YEAR, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) AS FiscalYear ,DATEPART(MONTH, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) AS FiscalMonth ,DATEPART(QUARTER, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) AS FiscalQuarter ,DATEPART(DAYOFYEAR, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) As FiscalJulianDay ,DATENAME(MONTH, DATEADD(DAY, n-1, @startDate)) AS [MonthName] ,DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) AS [DayOfMonth] ,DATEPART(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayNumber ,DATENAME(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayName ,ceiling(DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) / 7.0) As NthWeekday -- if you didn't need the IsHoliday function, simply comment out -- the following line ,dbo.fn_IsHoliday(DATEADD(DAY, n-1, @startDate)) AS IsHoliday FROM n; END GO
To get the nth week (in a month) in which a date falls, you can use the returnedNthWeekdaycolumn.
How coincidental - I just wrote some SQL code that creates a calendar, based on the specified date range. It's nothing more than a stored proc and a function that you implement in your database. Simply call the stored proc, and it returns the "calendar", which includes the calendar year, month, day, julian day and quarter, the fiscal year, month, quarter and julian day, as well as the nth weekday, nth week, and whether or not the date is a federal holiday (accounting for holidays that fall on weekends).
The business case that spurred this development was that the analysts were using a table that had data that didn't go any further than Dec 31 2016, and that had to manually be updated every year. My approach requires no tables, and can return calendar info for any range of dates.
When I return to work on Monday, I'll post it here.
这篇关于如何获得月份和月份的周数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
