espanolanthony
asked on
error :conversion of a char data type to a datetime data type
I have a error that is below:
(0 row(s) affected)
Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I have a very big sql script but this error point to a afecific area in the whole script so i am just sending you that part only. Please see below, the error is in the third line that says insert. Please help me out asap.
If EXISTS (SELECT * FROM sysobjects where name = 'ros_heent_')
BEGIN
insert into ros_heent_ (enterprise_id, practice_id, person_id, enc_id, created_by, create_timestamp, modified_by,modify_timesta mp,
rb_headache, visual_loss, rb_eyedischarge, rb_discharge, rb_nasaldischarge, rb_hearloss)
select ros.enterprise_id, ros.practice_id, ros.person_id, ros.enc_id, ros.created_by, ros.create_timestamp, ros.modified_by, isnull(ros.modify_timestam p,getdate( )),
case when datediff(mm, p.date_of_birth, ros.create_timestamp)/12 >= 19 then 0 else null end as rb_headache,
'N' as visual_loss,
case when datediff(mm, p.date_of_birth, ros.create_timestamp)/12 < 19 then 0 else null end as rb_eyedischarge,
case when datediff(mm, p.date_of_birth, ros.create_timestamp)/12 < 19 then 0 else null end as rb_discharge,
case when datediff(mm, p.date_of_birth, ros.create_timestamp)/12 < 19 then 0 else null end as rb_nasaldischarge,
0 as rb_hearloss
from review_of_system_ ros, person p
where ros.person_id = p.person_id and ros.nl_heent = 1
and enc_id not in (select enc_id from ros_heent_)
END
GO
(0 row(s) affected)
Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I have a very big sql script but this error point to a afecific area in the whole script so i am just sending you that part only. Please see below, the error is in the third line that says insert. Please help me out asap.
If EXISTS (SELECT * FROM sysobjects where name = 'ros_heent_')
BEGIN
insert into ros_heent_ (enterprise_id, practice_id, person_id, enc_id, created_by, create_timestamp, modified_by,modify_timesta
rb_headache, visual_loss, rb_eyedischarge, rb_discharge, rb_nasaldischarge, rb_hearloss)
select ros.enterprise_id, ros.practice_id, ros.person_id, ros.enc_id, ros.created_by, ros.create_timestamp, ros.modified_by, isnull(ros.modify_timestam
case when datediff(mm, p.date_of_birth, ros.create_timestamp)/12 >= 19 then 0 else null end as rb_headache,
'N' as visual_loss,
case when datediff(mm, p.date_of_birth, ros.create_timestamp)/12 < 19 then 0 else null end as rb_eyedischarge,
case when datediff(mm, p.date_of_birth, ros.create_timestamp)/12 < 19 then 0 else null end as rb_discharge,
case when datediff(mm, p.date_of_birth, ros.create_timestamp)/12 < 19 then 0 else null end as rb_nasaldischarge,
0 as rb_hearloss
from review_of_system_ ros, person p
where ros.person_id = p.person_id and ros.nl_heent = 1
and enc_id not in (select enc_id from ros_heent_)
END
GO
basically you need use convert on the one that it's currently char datatype based on the date format you have. Say that it's the create_timestamp column the char data type one. so
convert(datetime, ros.create_timestamp, 101) --I've used 101, but use the correct one the format you currently have.
Check this link for the different format options, choose the one suits you:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
convert(datetime, ros.create_timestamp, 101) --I've used 101, but use the correct one the format you currently have.
Check this link for the different format options, choose the one suits you:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
Also, which one of them is char data type?