问题描述
情景 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()).
这篇关于SQL Server:日期自动化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！