问题描述
正在寻找一种以11/1/2009"格式获取日期的方法,这将是下个月的第一个星期日.我想在 10 月的第一个星期日之后运行此查询以获得下个月的第一个星期日.使用 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?
谢谢
推荐答案
试试这个:
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))
编辑注释:
下个月的第一天由表达式给出:
The first of next Month is given by the expression:
DateAdd(Month, 1+DateDiff(Month, 0, @D), 0)
或通过:通过将 1 更改为 2,可以将其修改为从现在起两个月后的第一天:
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)
响应@NissanFan 和@Anthony:修改它以返回第一个星期一星期二星期三等,将值 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