SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strange datetime conversion issue


Strange datetime conversion issue

Author
Message
bochambers
bochambers
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 82
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?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64652 Visits: 17979
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 Modens 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)
bochambers
bochambers
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 82
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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97903 Visits: 38993
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?

Cool
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)
crmitchell
crmitchell
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1277 Visits: 1796
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.
bochambers
bochambers
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 82
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.
crmitchell
crmitchell
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1277 Visits: 1796
Does using
TOP 100 PERCENT
work?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97903 Visits: 38993
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.

Cool
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)
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