June 22, 2007 at 2:54 am
Hi there,
I am somewhat perplexed by a problem one of our clients is reporting with an application. He's getting this error.
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
He's running a 2005 in compatbality mode 80 (The databse was initially written on SQL 2000).
We are using an ADO.NET dataset to insert a record into the databse from a ASP.NET web application. I've had the client run a SQL profiler session to capture the actual insert statement generated by the dataset and it is listed at the bottom of this message.
Please note it's a large statement because the insert is part of a multi stage process broken out into functional units (web pages). Involves saving complex details regarding incidents.
An interesting side effect, on single stage processes, i.e. Saving employee details, which is a single page process we do not get this error, although any dates that are not completed are still entered as null.
Any suggestions gratefully received. The Client assures me date time settings are in English UK format for both the web server and database server.
Many thanks,
Andy
INSERT INTO INCIDENT_DETAILS( EMPLOYEE ,
EMPLOYEE_REF , SURNAME , FORENAME , EMPLOYEE_CATEGORY ,
EMPLOYEE_CATEGORY_OTHER
, JOBTITLE , JOBTITLE_OTHER , GENDER , DATE_OF_BIRTH , AGEGROUP ,
ADDRESS , POSTCODE , HOME_TELEPHONE , WORK_TELEPHONE , EMERGENCY_CONTACT
,
EMERGENCY_TELEPHONE , JOIN_DATE , LEAVE_DATE , CURRENT_EMPLOYEE ,
LINE_MANAGER , LINE_MANAGER_OTHER , FIRST_AIDER , FIRST_AID_EXPIRY ,
LEVEL1
, LEVEL2 , LEVEL3 , LEVEL4 , LEVEL5 , INCIDENT_DETAILS , RECORD_TYPE ,
INCIDENT_NO , INCIDENT_DATE , INCIDENT_TYPE , INCIDENT_TYPE_OTHER ,
LOCATION , LOCATION_OTHER , PROCESS , PROCESS_OTHER , SUBSTANCE ,
SUBSTANCE_OTHER , INJURY , INJURY_OTHER , DEPARTMENT , DEPARTMENT_OTHER
,
CAUSE , CAUSE_OTHER , CONTRIBUTORY_CAUSE , CONTRIBUTORY_CAUSE_OTHER ,
SEVERITY , ABSENT , DAYS_LOST , RETURN_TO_WORK_DATE , REPORTED_BY ,
DATE_REPORTED , REFERRED , DATE_REFERRED , ATTENDED , DATE_ATTENDED ,
TREATED , DATE_TREATED , CLAIM , F2508_VERBAL , F2508_SENT ,
F2508_COMPLETED , INCIDENT_TEXT , INCIDENT_TIME , PERIOD_YEAR ,
INCIDENT_PERIOD , HAPPENED_HERE , HAPPENED_WHERE , HAPPENED_ADDRESS ,
UNCONSCIOUS , RESUSCITATION , REMAIN_IN_HOSPITAL , RISK_ASSESSMENT_REF ,
INVESTIGATOR , INVESTIGATOR_JOBTITLE , INVESTIGATOR_JOBTITLE_OTHER ,
INVESTIGATION_START_DATE , INVESTIGATION_END_DATE ,
CONTRIBUTORY_FACTOR_1 , CONTRIBUTORY_FACTOR_2 , CONTRIBUTORY_FACTOR_3 ,
CONTRIBUTORY_FACTOR_4 , INVESTIGATION_COMMENTS , LINE_MANAGER_COMMENTS ,
DISEASE , ACTIVITY , OFF_SHORE , INFECTION_TYPE , WRITTEN_DIAGNOSIS
, DISEASE_DETAILS_EXPOSURE , DISEASE_DETAILS_OTHER ,
INTERNAL_PARTIES_REPORTED_TO , DATE_REPORTED_TO_INSURERS ,
INSURERS_CONTACT_DETAILS ,
MESSAGE_REF , LETTER_REF , LETTER_DATE , ACKNOWLEDGEMENT_DATE ,
DEFENCE_DATE , DEFENCE_INFORMATION , CLAIM_OUTCOME , MAN_HOURS_LOST ,
E_MAIL
, HOURS_PER_DAY_WORKED , LOCAL_REFERENCE , RECEIVED_CONFIRMATION ,
LAST_AMENDED_DATE , CREATED_DATE , NWUSER , HOW_HIGH_WAS_FALL ,
PERMISSION_GRANTED , ENFORCING_AUTHORITY_REFERENCE ,
RISK_ASSESSMENT_HEADER , MHA_STATUS , MHA_STATUS_OTHER ,
RESPONSIBLE_MEDICAL_OFFICER ,
FIRST_AID_ADMINISTERED , ADVISED_TO_SEE_GP , ADVISED_BY_NAME ,
ATTENDED_AE , ATTENDED_AE_DATE , ATTENDED_AE_TIME , OCCUPATIONAL_HEALTH
,
OCCUPATIONAL_HEALTH_DATE , OCCUPATIONAL_HEALTH_TIME , DOCTOR_REQUESTED ,
DOCTOR_REQUESTED_BY_NAME , DID_DOCTOR_RESPOND , INJURY_SUSTAINED ,
NAME_OF_DOCTOR , PATIENT_EXAMINED , PATIENT_EXAMINED_DATE ,
PATIENT_EXAMINED_TIME , REPORTED_TO_RIDDOR , REPORTED_TO_POVA ,
REPORTED_TO_HOME_OFFICE , REPORTED_TO_MHA ,
REPORTED_TO_HEALTH_COMMISSION , REPORTED_TO_PATIENTS_COMMISSIONERS ,
REPORTED_TO_POLICE ) VALUES
( @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8 , @p9 , @p10 , @p11 ,
@p12 , @p13 , @p14 , @p15 , @p16 , @p17 , @p18 , @p19 , @p20 , @p21 ,
@p22 , @p23 , @p24 , @p25 , @p26 , @p27 , @p28 , @p29 , @p30 , @p31 ,
@p32 , @p33 , @p34 , @p35 , @p36 , @p37 , @p38 , @p39 , @p40 , @p41 ,
@p42 , @p43 , @p44 , @p45 , @p46 , @p47 , @p48 , @p49 , @p50 , @p51 ,
@p52 , @p53 , @p54 , @p55 , @p56 , @p57 , @p58 , @p59 , @p60 , @p61 ,
@p62 , @p63 , @p64 , @p65 , @p66 , @p67 , @p68 , @p69 , @p70 , @p71 ,
@p72 , @p73 , @P74 , @p75 , @p76 , @p77 , @p78 , @p79 , @p80 , @p81 ,
@p82 , @p83 , @p84 , @p85 , @p86 , @p87 , @p88 , @p89 , @p90 , @p91 ,
@p92 , @p93 , @p94 , @p95 , @p96 , @p97 , @p98 , @p99 , @p100 , @p101 ,
@p102 , @p103 , @p104 , @p105 , @p106 , @p107 , @p108 , @p109 , @p110 ,
@p111 , @p112 , @p113 , @p114 , @p115 , @p116 , @p117 , @p118 , @p119
, @p120 , @p121 , @p122 , @p123 , @p124 , @p125 , @p126 , @p127 , @p128
, @p129 , @p130 , @p131 , @p132 , @p133 , @p134 , @p135 , @p136 ,
@p137 , @p138 , @p139 , @p140 , @p141 , @p142 , @p143 )',N'@p1
uniqueidentifier,@p2 nvarchar(4000),@p3 nvarchar(4000),@p4
nvarchar(4000),@p5
uniqueidentifier,@p6 nvarchar(4000),@p7 uniqueidentifier,@p8
nvarchar(4000),@p9 nvarchar(4000),@p10 datetime,@p11
uniqueidentifier,@p12
nvarchar(4000),@p13 nvarchar(4000),@p14 nvarchar(4000),@p15
nvarchar(4000),@p16 nvarchar(4000),@p17 nvarchar(4000),@p18
datetime,@p19
datetime,@p20 bit,@p21 uniqueidentifier,@p22 nvarchar(4000),@p23
bit,@p24 datetime,@p25 uniqueidentifier,@p26 uniqueidentifier,@p27
uniqueidentifier,@p28 uniqueidentifier,@p29 uniqueidentifier,@p30
uniqueidentifier,@p31 nvarchar(4000),@p32 nvarchar(4000),@p33
datetime,@p34
uniqueidentifier,@p35 nvarchar(4000),@p36 uniqueidentifier,@p37
nvarchar(4000),@p38 uniqueidentifier,@p39 nvarchar(4000),@p40
uniqueidentifier,@p41 nvarchar(4000),@p42 uniqueidentifier,@p43
nvarchar(4000),@p44 uniqueidentifier,@p45 nvarchar(4000),@p46
uniqueidentifier,@p47 nvarchar(4000),@p48 uniqueidentifier,@p49
nvarchar(4000),@p50 uniqueidentifier,@p51 bit,@p52 nvarchar(4000),@p53
datetime,@p54 nvarchar(4000),@p55 datetime,@p56 bit,@p57 datetime,@p58
bit,@p59 datetime,@p60 bit,@p61 datetime,@p62 nvarchar(4000),@p63
datetime,@p64 datetime,@p65 datetime,@p66 text,@p67 datetime,@p68
nvarchar(4000),@p69 nvarchar(4000),@p70 bit,@p71 nvarchar(4000),@p72
nvarchar(4000),@p73 bit,@p74 bit,@p75 bit,@p76 nvarchar(4000),@p77
nvarchar(4000),@p78 uniqueidentifier,@p79 nvarchar(4000),@p80
datetime,@p81 datetime,@p82 uniqueidentifier,@p83 uniqueidentifier,@p84
uniqueidentifier,@p85 uniqueidentifier,@p86 text,@p87 text,@p88
uniqueidentifier,@p89 uniqueidentifier,@p90 bit,@p91 nvarchar(4000),@p92
bit,@p93 text,@p94 text,@p95 text,@p96 datetime,@p97 text,@p98
nvarchar(4000),@p99 nvarchar(4000),@p100 datetime,@p101 datetime,@p102
datetime,@p103 text,@p104 text,@p105 decimal(10,1),@p106 bit,@p107
decimal(18,2),@p108 nvarchar(4000),@p109 bit,@p110 datetime,@p111
datetime,@p112 uniqueidentifier,@p113 decimal(18,2),@p114 bit,@p115
nvarchar(4000),@p116 uniqueidentifier,@p117 uniqueidentifier,@p118
nvarchar(4000),@p119 nvarchar(4000),@p120 bit,@p121 bit,@p122
nvarchar(4000),@p123 bit,@p124 datetime,@p125 datetime,@p126 bit,@p127
datetime,@p128 datetime,@p129 bit,@p130 nvarchar(4000),@p131 bit,@p132
bit,@p133 nvarchar(4000),@p134 bit,@p135 datetime,@p136 datetime,@p137
bit,@p138 bit,@p139 bit,@p140 bit,@p141 bit,@p142 bit,@p143
bit',@p1=NULL,@p2=NULL,@p3=NULL,@p4=NULL,@p5=NULL,@p6=NULL,@p7=NULL,@p8=
NULL,@p9=NULL,@p10=NULL,@p11=NULL,@p12=NULL,@p13=NULL,@p14=NULL,@p15=NUL
L,@p16=NULL,@p17=NULL,@p18=NULL,@p19=NULL,@p20=NULL,@p21=NULL,@p22=NULL,
@p23=NULL,@p24=NULL,@p25=NULL,@p26=NULL,@p27=NULL,@p28=NULL,@p29=NULL,@p
30='9ABA885D-0720-40F3-9890-8475ED16C7BE',@p31=N'E',@p32=N'0000000084',@
p33=NULL,@p34=NULL,@p35=NULL,@p36=NULL,@p37=NULL,@p38=NULL,@p39=NULL,@p4
0=NULL,@p41=NULL,@p42=NULL,@p43=NULL,@p44=NULL,@p45=NULL,@p46=NULL,@p47=
NULL,@p48=NULL,@p49=NULL,@p50=NULL,@p51=NULL,@p52=NULL,@p53=NULL,@p54=NU
LL,@p55=NULL,@p56=NULL,@p57=NULL,@p58=NULL,@p59=NULL,@p60=NULL,@p61=NULL
,@p62=NULL,@p63=NULL,@p64=NULL,@p65=NULL,@p66=NULL,@p67=NULL,@p68=NULL,@
p69=NULL,@p70=NULL,@p71=NULL,@p72=NULL,@p73=NULL,@p74=NULL,@p75=NULL,@p7
6=NULL,@p77=NULL,@p78=NULL,@p79=NULL,@p80=NULL,@p81=NULL,@p82=NULL,@p83=
NULL,@p84=NULL,@p85=NULL,@p86=NULL,@p87=NULL,@p88=NULL,@p89=NULL,@p90=NU
LL,@p91=NULL,@p92=NULL,@p93=NULL,@p94=NULL,@p95=NULL,@p96=NULL,@p97=NULL
,@p98=NULL,@p99=NULL,@p100=NULL,@p101=NULL,@p102=NULL,@p103=NULL,@p104=N
ULL,@p105=NULL,@p106=NULL,@p107=NULL,@p108=NULL,@p109=NULL,@p110=NULL,@p
111=''2007-06-21
14:46:56:083'',@p112='A086A7D2-C034-4583-BBA7-D014894C2739',@p113=NULL,@
p114=NULL,@p115=NULL,@p116=NULL,@p117=NULL,@p118=NULL,@p119=NULL,@p120=N
ULL,@p121=NULL,@p122=NULL,@p123=NULL,@p124=NULL,@p125=NULL,@p126=NULL,@p
127=NULL,@p128=NULL,@p129=NULL,@p130=NULL,@p131=NULL,@p132=NULL,@p133=NU
LL,@p134=NULL,@p135=NULL,@p136=NULL,@p137=NULL,@p138=NULL,@p139=NULL,@p1
40=NULL,@p141=NULL,@p142=NULL,@p143=NULL
June 22, 2007 at 2:59 am
You have to convert the Date_OF_Birth to ISO date.Because the date format inserted sql server 2005 changes according to Regional Setttins.Try converting your date format as given below
Private Function getISODate(ByVal tempDate As DateTime) As String
Dim strDate As String = ""
strDate = Microsoft.VisualBasic.Day(tempDate) & "-" & MonthName(Month(tempDate)) & "-" & Year(tempDate)
strDate = strDate & " " & Hour(tempDate) & ":" & Minute(tempDate) & ":" & Second(tempDate)
Return strDate
End Function
Hope this helps
June 22, 2007 at 4:35 am
Thanks Dana,
I always thought ISO 8601 standard was 'YYYY-MM-DD HH:MM:SS' which is the format used above?
This very same statement does seem to work fine on our test server 2005 server, it's also running in compatability mode. I wondered if there were dateformat settings that I need to be aware of in SQL Server 2005 that would prevent SQL from accepting the date time settings.
Andy
June 22, 2007 at 5:24 am
I faced the same file for problem.For that i wrote that format.If you try to insert using that format , that problem will be rectified. Try that.
June 22, 2007 at 8:49 am
The only format that is unambiguous to SQL Server in all circumstances is this:
YYYYMMDD HH:MM:SS.MSS, Example: 20071231 23:23:59.997
Any other format can have a conversion problem, including YYYY-MM-DD HH:MM:SS.MSS
Of course, the date must be a valid one.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply