The conversion of a varchar datatype to a datetime datatype resulted in an out-of-range value
将varchar数据类型转换为datetime数据类型会导致值超出范围
select a.DLNO,
a.NAME,
b.TOPSTRING,
Convert(datetime,a.DOB,103) as DOB,
Convert(datetime,a.DOI,103) as DOI,
Convert(datetime,b.datepushed,103) as datepushed
from
PLInfo_Demo a,DLInfo_Demo b
where
a.dlno=b.DLNO
2 个解决方案
#1
7
Type 103 requires that you have datetimes with the European date/month order: 'dd/mm/yyyy'
类型103要求您有datetimes和欧洲日期/月订单:'dd/mm/yyyy'
If you store month first, this may result in this error (say for '01/13/2012')
如果您首先存储月份，这可能导致这个错误(例如“01/13/2012”)。
If it's the case, use type 101 ('mm/dd/yyyy')
如果是这样，使用101类型(“mm/dd/ yyyyy”)
It's always better to store datetimes as DATETIME.
将datetimes存储为DATETIME总是更好的。
#2
0
sp_helptext sp_displayClinicRecordsbyclinicid
alter procedure sp_displayClinicRecordsbyclinicid
(
@id bigint,
@category char(1)
)
as
select id,ClinicName,
CONVERT(varchar(10), cast(Visitdate as datetime),101) Visitdate ,
CONVERT(varchar(19), cast(visitTime as datetime) ,101) visitTime ,
patient_first,patient_last,patient_address,patient_city,patient_state, CONVERT(varchar(10), cast(patient_dob as datetime),101) patient_dob,
precriber,prescriptionnum,CONVERT(varchar(10),cast(pharmacyfilldate as datetime) ,101) pharmacyfilldate,CONVERT(varchar(10),cast(prescriptionenddate as datetime) ,101) prescriptionenddate,drugname,drugformat,npi,dea,
licence,clinicid,Category,PatientFullname,Prescriber_first,Prescriber_last,Qty,Days
from tblClinicRecords where clinicid=@id and Category=@category
#1
7
Type 103 requires that you have datetimes with the European date/month order: 'dd/mm/yyyy'
类型103要求您有datetimes和欧洲日期/月订单:'dd/mm/yyyy'
If you store month first, this may result in this error (say for '01/13/2012')
如果您首先存储月份，这可能导致这个错误(例如“01/13/2012”)。
If it's the case, use type 101 ('mm/dd/yyyy')
如果是这样，使用101类型(“mm/dd/ yyyyy”)
It's always better to store datetimes as DATETIME.
将datetimes存储为DATETIME总是更好的。
#2
0
sp_helptext sp_displayClinicRecordsbyclinicid
alter procedure sp_displayClinicRecordsbyclinicid
(
@id bigint,
@category char(1)
)
as
select id,ClinicName,
CONVERT(varchar(10), cast(Visitdate as datetime),101) Visitdate ,
CONVERT(varchar(19), cast(visitTime as datetime) ,101) visitTime ,
patient_first,patient_last,patient_address,patient_city,patient_state, CONVERT(varchar(10), cast(patient_dob as datetime),101) patient_dob,
precriber,prescriptionnum,CONVERT(varchar(10),cast(pharmacyfilldate as datetime) ,101) pharmacyfilldate,CONVERT(varchar(10),cast(prescriptionenddate as datetime) ,101) prescriptionenddate,drugname,drugformat,npi,dea,
licence,clinicid,Category,PatientFullname,Prescriber_first,Prescriber_last,Qty,Days
from tblClinicRecords where clinicid=@id and Category=@category