問題描述

正在尋找一種以11/1/2009"格式獲取日期的方法，這將是下個(gè)月的第一個(gè)星期日.我想在 10 月的第一個(gè)星期日之后運(yùn)行此查詢以獲得下個(gè)月的第一個(gè)星期日.使用 T-SQL 查詢完成此操作的最佳方法是什么?

Looking for a way to get the date in the format "11/1/2009", which would be the first sunday of next month. I want to run this query after the first sunday in october to get the first sunday of the upcoming month. What is the best method to accomplish this with a T-SQL query?

謝謝

推薦答案

試試這個(gè):

Declare @D Datetime 
Set @D = [Some date for which you want the following months' first sunday]
Select DateAdd(day, (8-DatePart(weekday, 
    DateAdd(Month, 1+DateDiff(Month, 0, @D), 0)))%7, 
    DateAdd(Month, 1+DateDiff(Month, 0, @D), 0))

編輯注釋:

下個(gè)月的第一天由表達(dá)式給出:

The first of next Month is given by the expression:

DateAdd(Month, 1+DateDiff(Month, 0, @D), 0)

或通過:通過將 1 更改為 2，可以將其修改為從現(xiàn)在起兩個(gè)月后的第一天:

or by: which can be modified to give the first of the month two months from now by changing the 1 to a 2:

DateAdd(Month, 2+DateDiff(Month, 0, @D), 0) 

響應(yīng)@NissanFan 和@Anthony:修改它以返回第一個(gè)星期一星期二星期三等，將值 8 更改為 9、10、11 等....

In response to @NissanFan, and @Anthony: to modify this to return the first Monday Tuesday Wednesday, etc, change the value 8 to a 9, 10, 11, etc....

Declare @Sun TinyInt Set @Sun = 8
Declare @Mon TinyInt Set @Mon = 9
Declare @Tue TinyInt Set @Tue = 10
Declare @Wed TinyInt Set @Wed = 11
Declare @Thu TinyInt Set @Thu = 12
Declare @Fri TinyInt Set @Fri = 13
Declare @Sat TinyInt Set @Sat = 14
Declare @D Datetime, @FONM DateTime -- FirstofNextMonth 
Set @D = [Some date for which you want the following months' first sunday]
Set @FONM = DateAdd(Month, 1+DateDiff(Month, 0, @D),0)

Select 
  DateAdd(day, (@Sun -DatePart(weekday, @FONM))%7, @FONM) firstSunInNextMonth,
  DateAdd(day, (@Mon -DatePart(weekday, @FONM))%7, @FONM) firstMonInNextMonth,
  DateAdd(day, (@Tue -DatePart(weekday, @FONM))%7, @FONM) firstTueInNextMonth,
  DateAdd(day, (@Wed -DatePart(weekday, @FONM))%7, @FONM) firstWedInNextMonth,
  DateAdd(day, (@Thu -DatePart(weekday, @FONM))%7, @FONM) firstThuInNextMonth,
  DateAdd(day, (@Fri -DatePart(weekday, @FONM))%7, @FONM) firstFriInNextMonth,
  DateAdd(day, (@Sat -DatePart(weekday, @FONM))%7, @FONM) firstSatInNextMonth

這篇關(guān)于使用 T-SQL 獲取下個(gè)月的第一個(gè)星期日的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！