Link to home
Start Free TrialLog in
Avatar of espanolanthony
espanolanthonyFlag for United States of America

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_timestamp,
            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_timestamp,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
Avatar of ralmada
ralmada
Flag of Canada image

can you post some samples of date_of_birth and create_timestamp?
Also, which one of them is char data type?
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 
 
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of espanolanthony

ASKER

thanks