date, datetime 資料型別

gistfile1.sql

--傳入參數
DECLARE @DateFrom VARCHAR(10),
		@DateEnd  VARCHAR(10)
SET @DateFrom   = '2013/01/08'
SET @DateEnd    = '2013/01/10'
--暫存資料表
DECLARE @tbl_TMP TABLE (
	Name    VARCHAR(20),
	LogDate DATETIME
)
--測試資料
INSERT INTO @tbl_TMP VALUES ('Tom', '2013/1/10 10:11:12')
INSERT INTO @tbl_TMP VALUES ('Mary', '2013/1/09 09:20:55')
INSERT INTO @tbl_TMP VALUES ('Jack', '2013/1/08 20:40:38')

--因為LogDate是datetime，故只會撈出2筆
SELECT * FROM @tbl_TMP WHERE (LogDate BETWEEN '2013/1/8' AND '2013/1/10')
--轉換成date，就可撈出3筆
SELECT * FROM @tbl_TMP WHERE (CAST(LogDate AS DATE) BETWEEN '2013/1/8' AND '2013/1/10')
--如出現以下錯誤訊息，是因為Date型別，從MSSQL 2008才有
--類型 DATE 不是已定義的系統類型(Type DATE is not a defined system type.)
--請改用下列任一語法即可
SELECT * FROM @tbl_TMP WHERE (LogDate BETWEEN CAST(@DateFrom AS DATETIME) AND CAST(@DateEnd + ' 23:59:59' AS DATETIME))
SELECT * FROM @tbl_TMP WHERE (LogDate BETWEEN CAST(@DateFrom AS DATETIME) AND DATEADD(SECOND,-1,DATEADD(day,1,cast('2013/1/10' AS DATETIME))))