Link to home
Start Free TrialLog in
Avatar of Jeff S
Jeff SFlag for United States of America

asked on

SQL 2005 Error - Conversion failed when converting datetime from character string

First and foremost, the SQL is handled dynamically by the SQL Server. The coding in my WHERE clause may look odd to you, please disregard this as it is not an issue. I am getting the following error and would appreciate a hand. Thanks.

Msg 241, Level 16, State 1, Line 6
Conversion failed when converting datetime from character string.
/*** EDD Listing by Patient by Date by Provider **/

SET NOCOUNT ON


SELECT   DISTINCT
        dbo.FormatName('', pp.First, pp.Middle, pp.Last, pp.Suffix) AS PatientName,
        pp.PatientId,
        pp.Phone1,
        CONVERT(DATETIME, o.OBSVALUE, 102) AS EDCDate,
        p.ListName AS Provider,
        pp.Birthdate,
        DATEDIFF(d, pp.Birthdate, GETDATE()) / 365.25 AS Age,
        SUBSTRING(o.OBSVALUE, 1, 2) AS EDCMonth,
        SUBSTRING(o.OBSVALUE, 4, 2) AS EDCDay,
        RIGHT(o.OBSVALUE, 4) AS EDCYear
FROM    dbo.OBS AS o
        INNER JOIN dbo.OBSHEAD AS oh ON o.HDID = oh.HDID
        INNER JOIN dbo.PatientProfile AS pp ON o.PID = pp.PId
        INNER JOIN dbo.DoctorFacility AS p ON pp.DoctorId = p.DoctorFacilityId
WHERE   ( o.HDID = 2683 )
        AND o.XID = 1000000000000000000
        AND CONVERT(DATETIME, o.OBSVALUE) >= ISNULL('03/01/2011', '1/1/1900')
        AND CONVERT(DATETIME, o.OBSVALUE) < DATEADD(day, 1,
                                                    ISNULL('03/31/2011',
                                                           '1/1/3000'))
        AND --Filter on doctor
        ( ( NULL IS NOT NULL
            AND pp.DoctorID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )

Open in new window

SOLUTION
Avatar of MeDude21
MeDude21
Flag of South Africa 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
SOLUTION
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
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
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
SOLUTION
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
ps if your ID are int identities, and entered dates don't make sense, you could use the ID's as a guide to sequence.
You may need to put an IsDate test in before converting any dates, as I strongly suspect you have an invalidate or a date in a different format than expected.

Sharath has provided you with an example to hunt out those dodgy dates.

My suggestion is to also use that as error handling in your routine.
Avatar of Jeff S

ASKER

All -

On my client they had one value come back when I ran Sharaths code. It gave me back a 0. Is there somehow now I can exclude these from returning to my report if not a date now? Please help.
Avatar of Jeff S

ASKER

Thanks to all. Split points to be fair.
For one incorrect value I would just get the record fixed and add more stringent validation to the app where the data is initially generated.
If that's not within your scope then I'd suggest wrapping do a case within the convert

Convert(datetime, case obsvalue when '0' then '1900-01-01' else obsvalue end, 103)

Not pretty and will add extra processing but should work.

Cheers
Chris
Alternatively, since there is just the one bad value, would it be acceptable to update that one to '1900-01-01' permanently.