一直以來都是有了日期資料之後,針對此日期資料做處理,這次看到一個考題是相反的,反而是要得到一個日期。
declare
@dd varchar(10),
@week int, --第幾週(初始 = 0)
@weekname int; --星期幾(sunday = 0)
set @dd = '2017/12/31' --convert(char(10), dateadd(m, -2, getdate()), 111) --yyyy/mm/dd
set @week = 2
set @weekname = 2
select
dateadd(day, -day(@dd)+1, convert(datetime, convert(datetime, convert(date, @dd)))) 'first day of the month', --每月第一天
dateadd(week, @week, dateadd(day, -day(@dd)+1, convert(datetime, convert(datetime, convert(date, @dd))))), --取週別
datediff(week, 0, dateadd(week, @week, dateadd(day, -day(@dd)+1, convert(datetime, convert(datetime, convert(date, @dd)))))),
dateadd(week, datediff(week, 0, dateadd(week, @week, dateadd(day, -day(@dd)+1, convert(datetime, convert(datetime, convert(date, @dd)))))), @weekname) ans,
datename(weekday, @weekname)
結果如下:
自行替換 parameters: @dd, @week, @weekname
--
考語法沒道理用筆試啊。
declare
@dd varchar(10),
@week int, --第幾週(初始 = 0)
@weekname int; --星期幾(sunday = 0)
set @dd = '2017/12/31' --convert(char(10), dateadd(m, -2, getdate()), 111) --yyyy/mm/dd
set @week = 2
set @weekname = 2
select
dateadd(day, -day(@dd)+1, convert(datetime, convert(datetime, convert(date, @dd)))) 'first day of the month', --每月第一天
dateadd(week, @week, dateadd(day, -day(@dd)+1, convert(datetime, convert(datetime, convert(date, @dd))))), --取週別
datediff(week, 0, dateadd(week, @week, dateadd(day, -day(@dd)+1, convert(datetime, convert(datetime, convert(date, @dd)))))),
dateadd(week, datediff(week, 0, dateadd(week, @week, dateadd(day, -day(@dd)+1, convert(datetime, convert(datetime, convert(date, @dd)))))), @weekname) ans,
datename(weekday, @weekname)
結果如下:
自行替換 parameters: @dd, @week, @weekname
--
考語法沒道理用筆試啊。
留言
張貼留言
請留下你的身分~
可用「Google帳戶」登入,或使用「名稱/網址」!❤
用匿名的話,我真的不知道你是誰喔!