Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CAST CONVERT doesn't work


CAST CONVERT doesn't work

Author
Message
walter.habegger
walter.habegger
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 33
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 Studio 10.50.1600.1
Microsoft Analysis Services Client Tools 10.50.1600.1
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 8.0.7601.17514
Microsoft .NET Framework 2.0.50727.5466
Operating System 6.1.7601

Does anybody know why this could happen?

Thanks in advance, Walter
GilaMonster
GilaMonster
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53815 Visits: 44620
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
Abu Dina
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 3323
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

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
Abu Dina
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 3323
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

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
walter.habegger
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 33
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
ChrisM@Work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10136 Visits: 19211
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
Exploring Recursive CTEs by Example Dwain Camps
ssimmons 2102
ssimmons 2102
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 287
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
walter.habegger
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 33
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53815 Visits: 44620
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
walter.habegger
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 33
The Result is a set of 13184 lines, all of them with the value = 0
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search