• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

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

0
Jeff S
Asked:
Jeff S
  • 2
  • 2
  • 2
  • +3
4 Solutions
 
MeDude21Commented:
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
 
8080_DiverCommented:
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
 
SharathData EngineerCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
David ToddSenior DBACommented:
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
 
David ToddSenior DBACommented:
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
 
chrismcCommented:
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
 
Jeff SAuthor Commented:
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
 
Jeff SAuthor Commented:
Thanks to all. Split points to be fair.
0
 
chrismcCommented:
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
 
8080_DiverCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now