CAST CONVERT doesn't work

Question

Post reply

CAST CONVERT doesn't work

walter.habegger

Old Hand

Points: 364
More actions
March 18, 2013 at 3:08 am

#269895

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Abu Dina SSChampion Points: 14155 More actions · Answer 2

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

Abu Dina SSChampion Points: 14155 More actions · Answer 3

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

walter.habegger Old Hand Points: 364 More actions · Answer 4

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

ssimmons 2102 SSCommitted Points: 1634 More actions · Answer 6

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?

walter.habegger Old Hand Points: 364 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

walter.habegger Old Hand Points: 364 More actions · Answer 9

The Result is a set of 13184 lines, all of them with the value = 0

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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

walter.habegger Old Hand Points: 364 More actions · Answer 11

That's correct, and the reason why I use CAST CONVERT

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

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

walter.habegger Old Hand Points: 364 More actions · Answer 13

Sorry, I frogot to enter the time range in my query. If I enter it, the result nothing to display

ssimmons 2102 SSCommitted Points: 1634 More actions · Answer 14

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.