[MSSQL] Useful SQL Scripts - CalendarBase
Useful SQL scripts
DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME DECLARE @FiscalBeginMonth INT SELECT @StartDate = DATEADD(MONTH,-1,GETDATE()),@EndDate = GETDATE(),@FiscalBeginMonth = 7 WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1), E02(N) AS (SELECT 1 FROM E00 a, E00 b), E04(N) AS (SELECT 1 FROM E02 a, E02 b), E08(N) AS (SELECT 1 FROM E04 a, E04 b), E16(N) AS (SELECT 1 FROM E08 a, E08 b), E32(N) AS (SELECT 1 FROM E16 a, E16 b), cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32), CalendarBase as ( SELECT DateKey = n , CalendarDate = DATEADD(day, n - 1, @StartDate ) , FiscalYearBegin = DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n - 1, @StartDate )) - (12 + DATEPART(MONTH, DATEADD(day, n - 1, @StartDate )) - @FiscalBeginMonth) % 12, 0) FROM cteTally WHERE N <= DATEDIFF(day, @StartDate , @EndDate +1) ) SELECT DateKey , IsoDate = CONVERT(char(8), CalendarDate, 112) , CalendarDate , CalendarYear = YEAR(CalendarDate) , CalendarQuarter = (DATEPART(QUARTER,CalendarDate) ) , CalendarMonth = MONTH(CalendarDate) , CalendarDay = DATEPART(DAY, CalendarDate) , DayofWk = DATEPART(Dw, CalendarDate) , CalendarWeekOfMonth = DATEDIFF(week, DATEADD(day,1, CalendarDate-DAY(CalendarDate) + 1) -1, CalendarDate) +1 , WeekofYr = DATEPART(WEEK, CalendarDate) , DayofYr = DATEPART(DAYOFYEAR, CalendarDate) , NameMonth = DATENAME(Month, CalendarDate) , NameDay = DATENAME (Weekday,CalendarDate ) , FiscalYear = CASE WHEN MONTH(CalendarDate) < @FiscalBeginMonth THEN YEAR(CalendarDate) ELSE YEAR(CalendarDate) + 1 END , FiscalMonth = DATEDIFF( MONTH, FiscalYearBegin, CalendarDate) + 1 , FiscalWeek = DATEDIFF( WEEK, FiscalYearBegin, CalendarDate) + 1 , FiscalDay = DATEDIFF( day, FiscalYearBegin, CalendarDate) + 1 , FirstDateOfWeek = DATEADD(D, -DATEPART(Dw, CalendarDate)+2, CalendarDate) FROM CalendarBase
------------------------------------------
除非特别声明,文章均为原创,版权与博客园共有,转载请保留出处
BUY ME COFFEE


【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 10年+ .NET Coder 心语 ── 封装的思维:从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源!
· 周边上新:园子的第一款马克杯温暖上架