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

Strange datetime conversion issue Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 11:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 4:07 PM
Points: 101, Visits: 78
I have been using a query that joins several raw tables (nvarchar datatypes), has several conversions, nested queries, CASE WHEN statements. In other words it is messy, but I didn't create it nor do I have time to recreate it properly. It has been generating a very strange conversion error (the cause not the error itself is strange) where there is some kind of datetime conversion error when converting a string datatype. I have debugged every single field from every table in the query that is either converted explicitly or implicitly (these have all been updated to explicit conversions), with no problems present.

The error is very strange because if I change the select fields (none of which are converting to a datetime implicitly or otherwise) to * the error message goes away, in fact if I simply add ,* after the last field in the select list it also goes away. However I can't use this because don't want every single field from every table in the query.
Further, if I change the Select statement to SELECT TOP XXX where XXX is greater than the number of rows returned the error still does not occur. But if I use SELECT TOP 100 PERCENT the error occurs again.
This made me think that it might be an error in the query execution cache. Which I cleared and tried again with the same errors still occurring.
The database is set up to use Korean_Wansung_CI_AS on SQL Server Enterprise Edition v10.50.1600.1

This problem is not critical as I can set it to use SELECT TOP XXX such that XXX is far larger than will ever be actually required. It was just very strange behavior that I had never seen before.

Unfortunately, I can't provide the code or data both are quite confidential, however I'm quite sure that this is not directly related to the code itself, as the code is working using the two scenarios mentioned.

Has anyone seen this behavior and/or know of the cause?
Post #1443095
Posted Wednesday, April 17, 2013 8:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:32 PM
Points: 13,302, Visits: 12,168
We all certainly understand sensitive data. Many of us work with it all day long. I can't however imagine that a query is sensitive. The problem here is that you have asked for help but won't provide the details. Short of knowing what the table looks like and the query that retrieves the data we have no chance of helping. We can't see your screen and we don't know the data. Anything anyone offered would be a complete guess.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443280
Posted Wednesday, April 17, 2013 6:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 4:07 PM
Points: 101, Visits: 78
Thanks Sean,

I completely understand and if no-one can help I don't blame them, this question is more of a curiosity for me as I can work around the issue and that is sufficient. Unfortunately my company considers code such as this as its IP for a product that they've developed. I understand that without the code it is almost impossible to debug, so perhaps it would have been better to try to understand why SELECT TOP XXX would be different from a SELECT without a "top" number of rows when XXX is greater then the number of rows that query produces. Or even why adding ",*" to the end of the fields being selected would change the behavior of the query to prevent the conversion error?

Post #1443562
Posted Wednesday, April 17, 2013 9:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 23,299, Visits: 32,039
I have a question, have you tried recreating the problem using a set of tables that resemble the tables used in the query (data types and column order) with a similar query joining the on columns of the same data types?




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443582
Posted Friday, April 19, 2013 3:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:21 AM
Points: 206, Visits: 838
Well without knowing anything about the query, data or even the error message anything you get will be very general.



You will need to break down the query and understand in detail how it works and what part is failing.
I would start by identifying what record(s) and fields(s) is(are) causing the error.
If your query is using subqueries try running those in isolation - this could throw some light on why adding * works - are you trying to use a field hidden within a subquery but not returned from it?
Same applies for any unions or stored proc calls.
The TOP XXX would indicate that there is probably a few records causing problems so narrow that down - add additional clauses to help with this. If there is no sorting its probably recent as well.
Post #1444280
Posted Friday, April 19, 2013 3:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 4:07 PM
Points: 101, Visits: 78
The bazaar thing is that I've tried looking at the sub-queries, I've looked at every field referenced in every table, including looking at each and every implicit conversion. I would try to replicate the issue for you but suspect that it is some incredibly strange sequence of events that could not be replicated if I tried.
I suspect that the issue is somewhere SQL server is behaving in a way that it shouldn't, there is a conversion that uses a case when statement to exclude the values that would cause an error, if SQL server tried to convert this inside the case when then I would see this conversion error.
The TOP XXX rows shouldn't be relevant because the result set in the data used is under 1000 rows, if I set it at 1000 or more I get every single row without the error occurring.
Whilst I don't claim to be flawless in anyway, I'm almost certain that the issue stems from SQL Server processing something in the wrong order. When I say wrong order please don't assume me ignorant to the order that calculations are normally performed. I believe it must be either evaluating the JOIN criteria of one of the nested queries before evaluating the nested query properly, or evaluating the conversion of a field and applying the case when inside the conversion afterward.
For Example there is code that does this:
CONVERT(DATE, CASE WHEN [date_field_as_text] = '00000000' THEN NULL ELSE [date_field_as_text] END,112) as [DATE]

this occurs inside a nested query and is used to join to another table where the other table is correctly stored as a date. The join criteria is a combination of this date field and another string field where the string fields are certainly not date data types implicitly or otherwise. Further the date fields are correctly converted to dates
E.g.

ON a.ID = b.ID
And a.Date = b.Date

I can provide more information a bit later.

Sorry for not being more helpful.
Post #1444294
Posted Friday, April 19, 2013 4:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:21 AM
Points: 206, Visits: 838
Does using
TOP 100 PERCENT
work?
Post #1444297
Posted Friday, April 19, 2013 6:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 23,299, Visits: 32,039
I am not going to blame the problem on a bug in SQL Server. I am more likely to say there is a logic/data error somewhere in the code.

My suggestion at this point is rewrite the query doing it in steps making sure each step works as you move to the next until you have rewritten the query. Doing this you may find what is causing the error.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444344
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse