Solved

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

Posted on 2011-02-14
10
500 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:Jeff S
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 2

Assisted Solution

by:MeDude21
MeDude21 earned 125 total points
ID: 34892341
I suggest you check your data. It could be because the date which is stored in a varchar data type is not correctly. Make sure that all the date values are stored in the same format eg. yyyy-mm-dd.

In other words if one date value is stored as yyyy-mm-dd and another one as dd-mm-yy then SQL will try apply the date conversion in the same way to both.

If the dates are all stored in the same way, then make sure that they have valid dates eg. February can not have more than 29 days, April can not have a date greater that 30 etc...
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 125 total points
ID: 34892698
In the main SELECT you are converting the OBSVALUE column's value without checking to see if it is NULL but, in the WHERE clause, you check for it being NULL.  

If there is any chance of it beng a NULL, then you need to perform the same ISNULL check in the SELECT; if there is no chance of it being NULL, then you do not need to do the check in the WHERE clause. ;-)

As a passing observation, is there a reason you have the DISTINCT in the SELECT statement?  If you are getting multiple rows returned because of the JOINs, you may want to examine the constraints on the JOINs more closely.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 125 total points
ID: 34893140
Run this and check the non-dates in your table.
SELECT   DISTINCT o.OBSVALUE
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 --Filter on doctor
        ( ( NULL IS NOT NULL
            AND pp.DoctorID IN ( NULL )
          )
          OR ( NULL IS NULL )
        ) AND ISDATE(o.OBSVALUE) = 0

Open in new window

0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 125 total points
ID: 34894231
Hi,

My STRONG recommendation is to never store a date as a character field, but always as a datetime.

From '03/31/2011' you appear to be assuming dates are stored as MM/DD/YYYY.

HTH
  David
0
 
LVL 35

Expert Comment

by:David Todd
ID: 34894232
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.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 18

Expert Comment

by:chrismc
ID: 34894828
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.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 34898135
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.
0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 34898480
Thanks to all. Split points to be fair.
0
 
LVL 18

Expert Comment

by:chrismc
ID: 34898489
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
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34900373
Alternatively, since there is just the one bad value, would it be acceptable to update that one to '1900-01-01' permanently.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now