Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CAST CONVERT doesn't work Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 3:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:03 AM
Points: 6, Visits: 32
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
Post #1432111
Posted Monday, March 18, 2013 3:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1432119
Posted Monday, March 18, 2013 3:35 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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
Post #1432121
Posted Monday, March 18, 2013 3:36 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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
Post #1432122
Posted Monday, March 18, 2013 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:03 AM
Points: 6, Visits: 32
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
Post #1432166
Posted Monday, March 18, 2013 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 7,123, Visits: 13,496
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
Post #1432170
Posted Tuesday, March 19, 2013 11:14 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 9:08 AM
Points: 755, Visits: 230
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?
Post #1432795
Posted Wednesday, March 20, 2013 1:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:03 AM
Points: 6, Visits: 32
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.
Post #1433020
Posted Wednesday, March 20, 2013 1:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
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 2008, MVP
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

Post #1433031
Posted Wednesday, March 20, 2013 1:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:03 AM
Points: 6, Visits: 32
The Result is a set of 13184 lines, all of them with the value = 0
Post #1433035
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse