sql - 将字符转换为日期时间的问题

Question

我已经编写了以下代码，我正在尝试在该表中搜索特定时间段之间的行:

SELECT 'RWH' + P.Hospital_Number AS MRN,
       P.Date_of_birth, 
       pd.Clinic_date AS Date_Seen,  
       pd.Clinic_type_No
FROM (Patient_diabetes AS pd 
INNER JOIN Patients AS p ON pd.Patient_No = p.Patient_No) 
INNER JOIN Staff AS s ON pd.Seen_by1_No = s.Staff_No
WHERE (((pd.Clinic_type_No)=241))
--AND hospital_number like '%63028%'
AND CONVERT(datetime,pd.Clinic_Date,121) BETWEEN '02/04/2015' AND '24/09/2015'
ORDER BY p.Date_of_birth,
pd.Clinic_date DESC

但是我收到以下错误，有什么解决办法吗？:

Msg 242, Level 16, State 3, Line 2 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Answer 1

我建议你改:

AND CONVERT(datetime,pd.Clinic_Date,121) BETWEEN '02/04/2015' AND '24/09/2015'

对此:

AND CONVERT(date,pd.Clinic_Date,121) BETWEEN '2015-04-02' AND '2015-09-24'