如何在SQL Server中获取第二个和第四个星期六日期的列表

Question

我几乎是编写SQL查询的新手。在SQL Server的上下文中，如何获取第二个和第四个星期六日期的列表2016年？

Answer 1

作为派生表完成，只是为了显示逻辑，但如果您愿意，可以减少：

select *
from (
    select d2016, 
           datename( weekday, d2016 ) as wkdy,
           row_number( ) over ( partition by datepart( month, d2016 ), datename( weekday, d2016 ) order by d2016 ) as rn_dy_mth
    from (
        select dateadd( day, rn, cast( '2016-01-01' as date ) ) as d2016
        from (
            select row_number() over( order by object_id ) - 1 as rn
            from sys.columns
            ) as rn
        ) as dy
    ) as dy_mth
where rn_dy_mth in ( 2, 4 )
  and wkdy = 'Saturday' 
order by d2016

Answer 2

--DEFINE LIMITS FOR DAY
DECLARE @TODATE DATETIME, @FROMDATE DATETIME
SET @FROMDATE ='2010-01-01'
SET @TODATE = '2017-12-31'
;WITH DATESEQUENCE( [DATE] ) AS
(
    SELECT @FROMDATE AS [DATE]
        UNION ALL
    SELECT DATEADD(DAY, 1, [DATE])
        FROM DATESEQUENCE
        WHERE DATE < @TODATE
    )
, DATESATURDAY AS
(SELECT CAST(CAST(YEAR([DATE]) AS VARCHAR)+ 
       (CASE WHEN DATEPART(M,[DATE])<=9 THEN '0'+CAST(DATEPART(M,[DATE]) AS VARCHAR)
       ELSE CAST(DATEPART(M,[DATE]) AS VARCHAR) END ) AS NUMERIC)  AS MONTH_ID
        ,CONVERT(VARCHAR,[DATE],106) AS DAY_DESC
       ,UPPER(DATENAME(DW,[DATE]))AS DAY_NAME
FROM DATESEQUENCE )
,SECOND_FOURTH_SATURDAY AS
(SELECT *
,ROW_NUMBER() OVER (PARTITION BY MONTH_ID ORDER BY DAY_NAME) FALL_IN
FROM DATESATURDAY
WHERE DAY_NAME='SATURDAY')
SELECT * FROM SECOND_FOURTH_SATURDAY
WHERE FALL_IN IN(2,4)
OPTION (MAXRECURSION 10000)

Answer 3

您可以使用SQL中的以下查询获得一个月中的任何一个星期六。我在这里获取当前日期，您可以设置自己选择的日期以获取特定月份的周六

select DATEADD(dd, (14 - @@DATEFIRST - DATEPART(dw, DATEADD(MONTH, DATEDIFF(mm, 0,getdate()), 0))) % 7, DATEADD(MONTH, DATEDIFF(mm, 0, getdate()), 0)) as FirstSaturday,
DATEADD(dd,7,DATEADD(dd, (14 - @@DATEFIRST - DATEPART(dw, DATEADD(MONTH, DATEDIFF(mm, 0, getdate()), 0))) % 7, DATEADD(MONTH, DATEDIFF(mm, 0, getdate()), 0))) as SecondSaturday,
DATEADD(dd,14,DATEADD(dd, (14 - @@DATEFIRST - DATEPART(dw, DATEADD(MONTH, DATEDIFF(mm, 0, getdate()), 0))) % 7, DATEADD(MONTH, DATEDIFF(mm, 0, getdate()), 0))) as ThirdSaturday,
DATEADD(dd,21,DATEADD(dd, (14 - @@DATEFIRST - DATEPART(dw, DATEADD(MONTH, DATEDIFF(mm, 0, getdate()), 0))) % 7, DATEADD(MONTH, DATEDIFF(mm, 0, getdate()), 0))) as LastSaturday