SQL Server:日期自动化

2021-09-10 00:00:00 sql tsql sql-server

情景 1:当年

SQL 代码总是需要选择当年一月的最后一个星期日.例如(31-01-2016)

Always SQL code needs to pick The last Sunday of January for the current year. For ex(31-01-2016)

下面的 SQL 代码给出了上述问题的答案

Below SQL code gives answer for above question

select case when DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, convert(date, GetDate())))) = 1
        then DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, Convert(date, GetDate())))
        else DateAdd(d, -DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, GetDate()))) + 1, DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, convert(date, GetDate()))))
    end

情景 2:去年

代码总是需要选择上一年一月的最后一个星期日.对于前(01-02-2015)

Always code needs to pick The last Sunday of January for the Previous year. For ex(01-02-2015)

请问场景 2 的 SQL 代码是什么?

What will be the SQL code Scenario 2 Please?

一周从周日开始，周六结束.

Week start on Sunday and ends on Saturday.

推荐答案

这里有一个方法，使用 DatePart<的 day of week 选项 (w)/代码>:

Here's a way, using the day of week option (w) for DatePart: 

declare @StartDate date = GetDate()
declare @FirstDayofYear date = DATEADD(y, - DatePart(y, @StartDate) + 1, @StartDate)
declare @LastDayOfJanuary date = DateAdd(d, 30, @FirstDayofYear)

select case When DatePart(w, @LastDayofJanuary) = 1
        Then @LastDayofJanuary
        Else DateAdd(d, -DatePart(w, @LastDayOfJanuary) + 1, @LastDayOfJanuary)
    end

显然，您可以将 @StartDate 更改为您想要的任何日期，包括去年的这个日期:

Obviously, you can change @StartDate to whatever date you want, including this date last year:

declare @StartDate date = DateAdd(yy, -1, GetDate())

或者，如果您希望将其全部扩展为单个表达式，则使用 GetDate():

Or, if you want it all expanded into a single expression, here it is with GetDate():

 select case when DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, convert(date, GetDate())))) = 1
        then DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, Convert(date, GetDate())))
        else DateAdd(d, -DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, GetDate()))) + 1, DateAdd(d, 30, DATEADD(y, - DatePart(y, GETDATE()) + 1, convert(date, GetDate()))))
    end

请注意，这假设您的机器设置为从星期日开始的一周.如果您的一周从另一天开始，您可能需要对此进行调整，因为第 1 天将是不同的日期.

Note that this assumes that your machine is set with the week starting on Sunday. You might have to tweak this if you week starts on another day, because day 1 will be a different date.

编辑添加:上述公式将返回一月的最后一天，如果最后一天不是星期日，则返回一月最后一天之前的星期日.

Edited to add: The formula above will return the last day in January, or the Sunday previous to the last day in January if the last day isn't a Sunday.

但是根据问题中给出的示例，OP 对 1 月最后一个星期日的定义可以进入 2 月.对于这种情况，这里有一个更好的公式:

But the OP's definition of the last Sunday in January can go into February, based on the example given in the question. Here's a better formula for that situation:

declare @StartDate date = GetDate()
declare @FirstDayofYear date = DATEADD(y, - DatePart(y, @StartDate) + 1, @StartDate)
declare @LastDayOfJanuary date = DateAdd(d, 30, @FirstDayofYear)

select case When DatePart(w, @LastDayofJanuary) = 1
    Then @LastDayofJanuary
    Else DateAdd(d, 7 - DatePart(w, @LastDayOfJanuary) + 1, @LastDayOfJanuary)
end

扩展版本为:

select case When DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, GetDate()) + 1, GetDate()))) = 1
    Then DateAdd(d, 30, DATEADD(y, - DatePart(y, GetDate()) + 1, convert(date, GetDate())))
    Else DateAdd(
                d, 
                7 - DatePart(w, DateAdd(d, 30, DATEADD(y, - DatePart(y, GetDate()) + 1, GetDate()))) + 1, 
                DateAdd(d, 30, DATEADD(y, - DatePart(y, GetDate()) + 1, convert(date, GetDate())))
                )
end

转换它以返回上一年的价值

将 GetDate() 的所有实例替换为 DateAdd(yy, -1, GetDate()).

Replace all instances of GetDate() with DateAdd(yy, -1, GetDate()).

相关文章