Question

我想从规定日期开始一周,因为我与国家能源局一起努力,争取获得能源。

 Select DateName(WEEK, 2012-03-09 )

产出为10。我愿获得本周的起始日期和结束日期,如<代码>2012-03-04至2012-03-10。是否可能?

Answer 1

与此类似:

DECLARE @MyDate Date =  2012-03-09 ;

-- This gets you the SUNDAY of the week your date falls in...
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @MyDate) - 1), @MyDate);

-- This gets you the SATURDAY of the week your date falls in...
SELECT DATEADD(DAY, (7 - DATEPART(WEEKDAY, @MyDate)), @MyDate);

-- This will show the range as a single column
SELECT
  CONVERT(NVarChar, DATEADD(DAY, -(DATEPART(WEEKDAY, @MyDate) - 1), @MyDate))
  +   through   +
  CONVERT(NVarChar, DATEADD(DAY, (7 - DATEPART(WEEKDAY, @MyDate)), @MyDate));

Answer 2

尝试如下,修改日期至日期

Select 
DateAdd(d, 1- DatePart(dw,GetDate()),GetDate()) FirstDayOfWeek,
DateAdd(d, 7- DatePart(dw,GetDate()),GetDate()) LastDayOfWeek

Answer 3

这并不取决于您的缺席确定日期。

set datefirst 4 -- this row does nothing in this query. 
                -- It will hurt the queries using datepart though
declare @t table(dt datetime)
insert @t values( 2012-03-09 11:12 ), ( 2012-03-10 11:12 ),( 2012-03-11 11:12 )

-- week from sunday to saturday 
Select dateadd(week, datediff(week, 0, dt),-1),
       dateadd(week, datediff(week, 0, dt),+5)
from @t

友情链接