Sql-Server-2005
日期時間僅作為日期
我了解 SQL Server 2005 不支持
DATE，因此無法將日期時間轉換為日期。我想要做的是列出目前一周一整天內具有日期時間的所有記錄。
所以我需要在本週的星期一創建的所有記錄忽略時間戳。目前我正在使用以下內容，它確實顯示了本週的周一至週五，但包括時間戳，這意味著我將看到不同的輸出，具體取決於查詢的執行時間：
set datefirst 1 select DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Monday, DATEADD(day, 2 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Tuesday, DATEADD(day, 3 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as wednesday, DATEADD(day, 4 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Thursday, DATEADD(day, 5 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) as Friday有人可以幫忙嗎？
我的預期輸出將是當週每一天的一列，該週從星期一開始（即：Datefirst 1）。最終，我將在一個案例陳述中使用它，例如：
CASE WHEN DateColumn >= DATEADD(day, 1 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) and DateColumn < DATEADD(day, 2 - DATEPART(dw, GETDATE()), CONVERT( DATETIME, GETDATE())) THEN IntegerColumn ELSE 0 END AS Monday…重複一周中的每一天。我需要容納整個星期一添加的 IntegerColumn 值。如果在星期一的幾個小時內沒有出現 DateColumn 條目，則顯示零。
今天的日期是 2016-02-11，此查詢返回
2016-02-11 00:00:00.000：SELECT curDay = DATEADD(day, DATEDIFF(dd, 0, getdate()), 0)然後可以將其包含在
DATEADD語句中：SELECT curDay = GETDATE() , DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Monday , DATEADD(day, 2 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Tuesday , DATEADD(day, 3 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as wednesday , DATEADD(day, 4 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Thursday , DATEADD(day, 5 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) as Friday這將給出以下輸出：
curDay | Monday | Tuesday | Wednesday | Thursday | Friday 2016-02-11 11:54:24.947 | 2016-02-08 00:00:00.000 | 2016-02-09 00:00:00.000 | 2016-02-10 00:00:00.000 | 2016-02-11 00:00:00.000 | 2016-02-12 00:00:00.000此查詢返回星期日的日期（
2016-02-06本週）：SELECT Sunday = DATEADD(day, DATEDIFF(dd, 0, getdate()) - DATEPART(weekday, GETDATE()), 0)它可以與此查詢一起使用，並提供相同的輸出：
SELECT curDay , DATEADD(day, 1, Sunday) as Monday , DATEADD(day, 2, Sunday) as Tuesday , DATEADD(day, 3, Sunday) as wednesday , DATEADD(day, 4, Sunday) as Thursday , DATEADD(day, 5, Sunday) as Friday FROM (SELECT Sunday = DATEADD(day, DATEDIFF(dd, 0, getdate()) - DATEPART(weekday, GETDATE()), 0)) as cd對於日期為星期一的記錄，可以使用此查詢：
SELECT ... FROM ... WHERE colDate >= DATEADD(day, 1 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0) AND colDate < DATEADD(day, 2 - DATEPART(weekday, getdate()) + DATEDIFF(dd, 0, getdate()), 0)它輸出日期大於或等於星期一 00:00 和星期二之前 00:00 的行。
使用起來
date >= '20160101 00:00' and date < '20160102 00:00'比BETWEEN. 請參閱：為什麼我的查詢搜尋日期時間不匹配？完整範例（SQL Fiddle）：
CREATE TABLE test(id int, [date] datetime) INSERT INTO test values(1, '2016-02-07 23:23') INSERT INTO test values(2, '2016-02-08 01:01') INSERT INTO test values(3, '2016-02-08 12:00') INSERT INTO test values(4, '2016-02-08 23:23') INSERT INTO test values(5, '2016-02-09 00:00') DECLARE @date datetime = '2016-02-11 12:34' set datefirst 1 -- Query 1 SELECT id, [date] , IntegerColumn = CASE WHEN [date] >= DATEADD(day, 1 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0) AND [date] < DATEADD(day, 2 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0) THEN IntegerColumn ELSE 0 END FROM test -- Query 2 SELECT id, [date], IntegerColumn FROM test WHERE [date] >= DATEADD(day, 1 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0) AND [date] < DATEADD(day, 2 - DATEPART(weekday, @date) + DATEDIFF(dd, 0, @date), 0)查詢一：
id | date | IntegerColumn 1 | 2016-02-07 23:23:00.000 | 0 2 | 2016-02-08 01:01:00.000 | 2 3 | 2016-02-08 12:00:00.000 | 3 4 | 2016-02-08 23:23:00.000 | 4 5 | 2016-02-09 00:00:00.000 | 0查詢 2：
id | date | IntegerColumn 2 | 2016-02-08 01:01:00.000 | 2 3 | 2016-02-08 12:00:00.000 | 3 4 | 2016-02-08 23:23:00.000 | 4