CAST CONVERT doesn't work

  • Hello everybody

    I have th following CAST Convert statement:

    SELECT CAST(CONVERT(CHAR(8), EZACDT) AS DATE) AS Accounting_Date

    FROM SYM_SPT_SNI_01.dbo.DBA_MVX_CINACC00

    WHERE EZCONO = 2

    AND EZDIVI = '001'

    And get the following error:

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string.

    This happens since last week, and our IT isn't able to give me any information about installed updates (see Server information below).

    Microsoft SQL Server Management Studio10.50.1600.1

    Microsoft Analysis Services Client Tools10.50.1600.1

    Microsoft Data Access Components (MDAC)6.1.7601.17514

    Microsoft MSXML3.0 4.0 5.0 6.0

    Microsoft Internet Explorer8.0.7601.17514

    Microsoft .NET Framework2.0.50727.5466

    Operating System6.1.7601

    Does anybody know why this could happen?

    Thanks in advance, Walter

  • There's something in that column that won't convert to date.

    Try this, see what you get:

    SELECT CONVERT(CHAR(8), EZACDT)

    FROM SYM_SPT_SNI_01.dbo.DBA_MVX_CINACC00

    WHERE EZCONO = 2

    AND EZDIVI = '001'

    AND ISDATE(CONVERT(CHAR(8), EZACDT)) = 0;

    Not a perfect check, but should get you started in finding the bad data.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • One ore more entries for the EZACDT column are not valid date formats.

    You can add this to your query but it will skip records with invalid date format.

    AND ISDATE(CONVERT(CHAR(8), EZACDT) = 1

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • I'm too slow.. lol

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • Thank you both for your replies, as a result, I saw that all data of the source field where correct.

    I removed the .NET Framework 3.5.1 (KB2789645) and 4 (KB2789642) Updates.

    now it works.

    Thanks for your help, Walter

  • walter.habegger (3/18/2013)


    Thank you both for your replies, as a result, I saw that all data of the source field where correct.

    I removed the .NET Framework 3.5.1 (KB2789645) and 4 (KB2789642) Updates.

    now it works.

    Thanks for your help, Walter

    Regarding both updates, Microsoft Support state the following:

    "Note We do not recommend that you remove any security update."

    I'd be very interested to see any documentary evidence that these updates affect CAST or CONVERT in SQL Server.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I am also curious why the removal of those security updates had anything to do with this. Its obvious to me that you are accessing Lawson tables. since EZACDT is a number value, I suspect you either have null values or some weird number that doesn't convert to a date. I don't know if you know that the source of CINACC is from table MITTRA. I have seen some odd Transaction dates in MITTRA (which become the EZACDT date in CINACC) These dates are at times manually entered with no validation and users fat finger them. You also seem to be using a View of CINACC, is there some sort of logic or conversion going on in the view?

  • You are right; the source is a Lawson Table. The DB2 DB is transferred daily to a SQL Server DB. I checked every field in CINACC and all EZTRDT were correct, that means all entries have a value in the requested range 20130101 to 20130319. I don't have another answer yet, and the result is also suspected for me.

  • What does this return?

    SELECT CONVERT(CHAR(8), EZACDT)

    FROM SYM_SPT_SNI_01.dbo.DBA_MVX_CINACC00

    WHERE EZCONO = 2

    AND EZDIVI = '001'

    AND ISDATE(CONVERT(CHAR(8), EZACDT)) = 0;

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The Result is a set of 13184 lines, all of them with the value = 0

  • That exact query returns over 13000 rows that fail the isDate check?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • That's correct, and the reason why I use CAST CONVERT

  • The thing is, 0 can't be cast to a date.

    DECLARE @SomeDate CHAR(8) = '0'

    SELECT ISDATE(@SomeDate), CAST(@SomeDate AS DATE)

    Msg 241, Level 16, State 1, Line 3

    Conversion failed when converting date and/or time from character string.

    So if you have thousands of rows with a 0 in, you will get conversion error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sorry, I frogot to enter the time range in my query. If I enter it, the result nothing to display

  • If you add in your original query in the where statement 'And EZACDT <> 0' if it will work. If the MO's don't get closed you will have 0 in that date field. I did your Cast(Convert statement against my CINACC table and don't have an issue as long as I filter out the records with 0 in the EZACDT field.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply