[SQL SERVER][TSQL]如何取得一週的資料
由於自己想使用TSQL查詢一週的 jobhistory 資料,自己紀錄一下
declare @mydate date='2011/04/12'
declare @start as int,@end as int
--星期日為每週的第一天(default)
set @start= CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, @mydate), -1),112 ) as int )
set @end=CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, @mydate), 5),112 ) as int )
--get a week of data
select * from msdb.dbo.sysjobhistory
where run_date between @start and @end
下面我將7天日期都列出來,大家應該可以更了解
--星期日為每週的第一天(default)
--SET DATEFIRST 7
declare @mydate date='2011/04/12'
select CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, @mydate), -1),112 ) as int ) as '該週第1天'
,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, @mydate), 0),112 ) as int ) as '該週第2天'
,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, @mydate), 1),112 ) as int ) as '該週第3天'
,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, @mydate), 2),112 ) as int ) as '該週第4天'
,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, @mydate), 3),112 ) as int ) as '該週第5天'
,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, @mydate), 4),112 ) as int ) as '該週第6天'
,CAST( CONVERT (varchar(8), DATEADD(wk, DATEDIFF(wk, 0, @mydate), 5),112 ) as int ) as '該週第7天'
這裡我簡單說一下, DATEDIFF(wk, 0, @mydate) 計算輸入日期和 1900-01-01 00:00:00.000 日期之間的週數,
再透過 DATEADD(wk, DATEDIFF(wk, 0, @mydate), -1) 取得該週第一天日期,由於我每週第一天是星期日,
所以要加 –1 ,但如果你每週的第一天是星期一,那這裡就要改成 DATEADD(wk, DATEDIFF(wk, 0, @mydate), 0),
其他以此類推.
參考