Solved

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

Posted on 2011-02-14
10
508 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 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