如何从sql server 2008中的日期获取月份的周数 [英] How to get week number of the month from the date in sql server 2008

问题描述

microsoft sql server 中的 SQL Statement 有一个内置函数来获取周数，但它是一年中的周.

In SQL Statement in microsoft sql server, there is a built-in function to get week number but it is the week of the year.

Select DatePart(week, '2012/11/30') // **returns 48**

返回值 48 是一年中的周数.

The returned value 48 is the week number of the year.

我想要1、2、3 或4(一个月的周数)而不是48.我认为本月的周数可以通过带有本周月数的模块来实现.例如

Instead of 48, I want to get 1, 2, 3 or 4 (week number of the month). I think the week number of the month can be achieved by modules with Month Number of this week. For e.g.

Select DATEPART(week, '2012/11/30')%MONTH('2012/11/30')

但我想知道是否有其他内置函数可以在 MS SQL SERVER 中获取本月的 WeekNumber.

But I want to know is there other built-in functions to get WeekNumber of the month in MS SQL SERVER.

推荐答案

这里有 2 种不同的方式，都假设一周从星期一开始

Here are 2 different ways, both are assuming the week starts on monday

如果您希望几周是完整的，那么它们属于它们开始的月份:所以星期六 2012-09-01 和星期日 2012-09-02 是第 4 周，星期一 2012-09-03 是第 1 周，使用这个:

If you want weeks to be whole, so they belong to the month in which they start: So saturday 2012-09-01 and sunday 2012-09-02 is week 4 and monday 2012-09-03 is week 1 use this:

declare @date datetime = '2012-09-01'
select datepart(day, datediff(day, 0, @date)/7 * 7)/7 + 1

如果您的周因月份变化而减少，那么 2012-09-01 星期六和 2012-09-02 星期日是第 1 周，2012-09-03 星期一是第 2 周，请使用此:

If your weeks cut on monthchange so saturday 2012-09-01 and sunday 2012-09-02 is week 1 and monday 2012-09-03 is week 2 use this:

declare @date datetime = '2012-09-01'
select datediff(week, dateadd(week, 
  datediff(day,0,dateadd(month,
    datediff(month,0,@date),0))/7, 0),@date-1) + 1

我收到了 Gerald 的电子邮件.他指出了第二种方法的缺陷.现在应该修复这个问题

I recieved an email from Gerald. He pointed out a flaw in the second method. This should be fixed now

这篇关于如何从sql server 2008中的日期获取月份的周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！