我有一个关于 SQL Server 中的日期时间表的问题。
这是我的代码。我可以每天做
SELECT DAYDATE AS BASE_DT
FROM(
SELECT CONVERT(CHAR(10), DATEADD(D, NUMBER, '2019-01-01'), 120) AS DAYDATE
FROM MASTER..SPT_VALUES WITH(NOLOCK)
WHERE TYPE = 'P'
AND NUMBER <= DATEDIFF(D, '2019-01-01', getdate())
) DATE_TABLE
我可以制作每日专栏,但我不知道如何添加周、月专栏
像这样。
请检查这个问题。谢谢你。
您可以使用以下查询:
SET DATEFIRST 1; -- Set first day of week to Monday
SELECT
DAYDATE AS BASE_DT
-- last day (always a Sunday) of the current week
, CONVERT(DATE, DATEADD(D, 7 - DATEPART(WEEKDAY, DAYDATE), DAYDATE)) AS WEEK_DT
-- last day of the current month
, CONVERT(DATE, DATEADD(month, ((YEAR(DAYDATE) - 1900) * 12)
+ MONTH(DAYDATE), -1)) AS MONTH_DT
FROM (
SELECT CONVERT(DATE, DATEADD(D, NUMBER, '2019-01-01')) AS DAYDATE
FROM MASTER..SPT_VALUES WITH(NOLOCK)
WHERE TYPE = 'P'
AND NUMBER <= DATEDIFF(D, '2019-01-01', getdate())
) DATE_TABLE
输出:
BASE_DT WEEK_DT MONTH_DT
----------------------------------
2019-01-01 2019-01-06 2019-01-31
2019-01-02 2019-01-06 2019-01-31
2019-01-03 2019-01-06 2019-01-31
2019-01-04 2019-01-06 2019-01-31
...
2019-02-01 2019-02-03 2019-02-28
2019-02-02 2019-02-03 2019-02-28
2019-02-03 2019-02-03 2019-02-28
...
2021-01-09 2021-01-10 2021-01-31
2021-01-10 2021-01-10 2021-01-31
2021-01-11 2021-01-17 2021-01-31
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句