在sql server中给定月份的最后一个星期五

How do i get the date for last friday of the month in T-SQL?

如何在T-SQL中获取本月最后一个星期五的日期?

I will be passing the year and month as parameter,e.g, 201211. If I pass '201211' as parameter it should return me '20121130' as answer as it's the date of last friday of month of november'12.

我将把年份和月份作为参数传递,例如,201211。如果我通过'201211'作为参数,它应该返回'20121130'作为答案,因为它是11月的最后一个星期五的日期。

2 个解决方案

#1

The 5 January 1900 was a Friday. This uses that a base date and calculates the last Friday in any given month though you must give it a date during the month rather than just the month itself. Replace the 2012-12-01 in this with a date in your month

1900年1月5日是星期五。这使用了基准日期并计算任何给定月份的最后一个星期五,但您必须在月份期间给它一个日期而不仅仅是月份本身。将2012-12-01替换为您当月的日期

SELECT DATEADD(DY,DATEDIFF(DY,'1900-01-05',DATEADD(MM,DATEDIFF(MM,0,'2012-12-01'),30))/7*7,'1900-01-05')

You can also use this to get the last Saturday by replacing the 1900-01-05 WITH 1900-01-06 etc.

你也可以使用它来取代1900-01-05与1900-01-06等的最后一个星期六。

#2

This would be much simpler using a calendar table; after creating the appropriate columns for your own needs you can just write this:

使用日历表会更简单;根据自己的需要创建适当的列后,您可以写下:

select 
    max([Date])
from 
    dbo.Calendar
where 
    YearAndMonth = 201211 and 
    DayOfWeek = 'Friday'

A calendar table is generally a much better solution for determining dates than using functions because the code is much more readable and you can use your own definition of things like WeekNumber, FinancialQuarter etc. that vary widely between countries and even companies.

日历表通常是确定日期比使用函数更好的解决方案,因为代码更具可读性,您可以使用自己定义的东西,如WeekNumber,FinancialQuarter等,在国家甚至公司之间差异很大。

#1

SELECT DATEADD(DY,DATEDIFF(DY,'1900-01-05',DATEADD(MM,DATEDIFF(MM,0,'2012-12-01'),30))/7*7,'1900-01-05')