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
9
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
3
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
9
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
3
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等,在国家甚至公司之间差异很大。