MS SQL Statement Datetime Problem

请问要怎么正确的retrieve/format (in sql) a datetime datatype field value from a table?

contractdate (datetype = datetime)
---------------------------------
18-Aug-2005 02:31:00 PM
18-Aug-2005 04:22:54 PM
26-Aug-2005 02:50:10 PM
31-Aug-2005 12:32:10 PM

When the following query is run,
SELECT contractno, contractdate, CONVERT(CHAR(20), contractdate, 5) AS formatcontractdate
FROM tblcontractbooking
WHERE (CONVERT(CHAR(20), contractdate, 5) BETWEEN '18-08-05' AND '18-10-05')

Wrong Result :

contractdate
------------
18-Aug-2005 02:31:00 PM
18-Aug-2005 04:22:54 PM


SELECT contractno, contractdate, CONVERT(CHAR(20), contractdate, 5) AS formatcontractdate
FROM tblcontractbooking
WHERE (CONVERT(CHAR(20), contractdate, 5) BETWEEN '18-08-05' AND '31-10-05')

Correct Result :

contractdate
------------
18-Aug-2005 02:31:00 PM
18-Aug-2005 04:22:54 PM
26-Aug-2005 02:50:10 PM
31-Aug-2005 12:32:10 PM

我应该要怎么format the datetime field to get the correct result in both senario without chaging the datatype?

By right, no matter '18-10-05' or '31-10-05', it should return 4 records.

先说声谢谢。。。

[ 本帖最后由 weeweett 于 1-9-2005 02:06 PM 编辑 ]

goatstudio

可以尝试:

SELECT contractno, contractdate, CONVERT(CHAR(20), contractdate, 5) AS formatcontractdate
FROM tblcontractbooking
WHERE contractdate BETWEEN CAST('8-18-2005' AS DateTime) AND CAST('10-18-2005' AS DateTime)

SELECT contractno, contractdate, CONVERT(CHAR(20), contractdate, 5) AS formatcontractdate
FROM tblcontractbooking
WHERE contractdate >= '2005-08-18 00:00:00'
AND contractdate  < '2005-11-01 00:00:00'

因為是 DateTime的關系，所以 SQL SERVER 也會把時間給計算上去。