Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » Administration » CAST CONVERT doesn't work
Add to briefcase
16 posts, Page 1 of 2
12»»

CAST CONVERT doesn't work Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
walter.habegger
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
GilaMonster
Posted Monday, March 18, 2013 3:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686, Visits: 29,943
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
Abu Dina
Posted Monday, March 18, 2013 3:35 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:45 AM
Points: 489, Visits: 2,138
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

-----------------------------------
http://www.SQL4n00bs.com
Post #1432121
Abu Dina
Posted Monday, March 18, 2013 3:36 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:45 AM
Points: 489, Visits: 2,138
I'm too slow.. lol

-----------------------------------
http://www.SQL4n00bs.com
Post #1432122
walter.habegger
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
ChrisM@Work
Posted Monday, March 18, 2013 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 5,608, Visits: 10,970
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
ssimmons 2102
Posted Tuesday, March 19, 2013 11:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:58 AM
Points: 411, Visits: 96
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
walter.habegger
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
GilaMonster
Posted Wednesday, March 20, 2013 1:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686, Visits: 29,943
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
walter.habegger
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
16 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.