sql - 查询从当前日期开始的最后 4 个星期二

Question

我正在做双向报告。所以我有一张桌子

occupancy ID | rent amount | Date of payment

租金在每周的每个星期二支付。我需要一个 SQL 查询来查找从当前日期开始的最后 4 个星期二。随着当前日期的继续，它需要是动态的，它需要自动获取前 4 个星期二

标签： sqlsql-date-functions

Answer 1

我猜，您正在为日期时间函数寻找正确的语法。这里是周数，月..等的示例以及如何使用它。希望对您的查询有所帮助。

DECLARE @today         DATETIME = GETDATE()
DECLARE @todayDayName        VARCHAR(10) = DATENAME(WEEKDAY, @today)
        , @todayShortDay       VARCHAR(10) = CONVERT(VARCHAR(10), @today, 103)
        , @todayDATE           DATE        = CAST(@today AS DATE)
        , @todayDayNumber      INT         = DATEPART(DAY, @today)
        , @todayWeekNumberYear INT         = DATEPART(wk, @today)
        , @todayWeekNumber     INT         = DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, @today), 0), @today) +1
        , @month               INT         = MONTH(@today)
select 
    @today as DayNow, 
    @todayDayName [TodayDayName], 
    @todayShortDay [TodayShortDay], 
    @todayDATE todayDATE, 
    @todayDayNumber [TodayDayNumber], 
    @todayWeekNumberYear TodayWeekNumberYear, 
    @todayWeekNumber TodayWeekNumber, 
    @month [month]


select top 100 
    UpdateDate, 
    [date] = CAST(UpdateDate AS DATE), 
    [DateName] = DATENAME(WEEKDAY, UpdateDate), 
    WeekNumber = DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, UpdateDate), 0), UpdateDate) +1,
    monthnumber = MONTH(UpdateDate)
from [dbo].TABLE 
where DATENAME(WEEKDAY, UpdateDate) = 'Tuesday'