Same Proc, Similar data different outcome across environments.

[FatalExceptionError]

Problem
"Syntax error converting datetime from character string."

I know what this means and I even know the exact record causing this problem. What I can't figure out is why I am getting this error.

Details
The linecode is a char field but the application that populates it defaults it to a string like yyyymmdd based on the current date. It has to manually be changed by a user.

Kicker #1
The record/s in question do not satisfy any of the parameters in the where clause. This means it technically is not a data issue because it is a legit entry (for another customer) This leads me to believe the profiler is executing a table scan or using a index and then it hits that final clause in the procedure and BOOM.

Kicker #2
The results are not consistent across my environments. This is my real concern. Dev and QA do not throw this error but production does (yay). My first guess is that an index is being used in the other two environments most likely due to different statistics because of actual usage. My other guess is there is a setting in SQL server that might be different but I highly doubt that. The DB and server team stay on top of consistent settings for compliance reasons. The only other difference is in hardware and I used (option maxdop 1) with the same result.

Questions
1. Can someone enlighten me on anything else going on that might cause this?
2. Is there someway for me to handle string data before the cast?

Code
Actual code changed to something closer to plain english. I can post the exact code for the stored proc if someone thinks it is needed but I am pretty sure the details outlined should suffice. Bold code is where I believe my error is happening.

SELECT
	header.document,
	header.site,
	header.order,
	lines.line_status,
	lines.line_number ,
	CAST(lines.line_code AS DATETIME) AS 'Date',
	lines.product,
	lines.description,
	lines.line_status,
	lines.line_status_description,
	ISNULL(lines.license,''),
	ISNULL(lines.qty, 0),
	product.product_description
FROM
	header WITH (NOLOCK)
INNER JOIN lines WITH (NOLOCK) ON lines.document = header.document
LEFT JOIN transactions WITH (NOLOCK) ON transactions.line_number = lines.line_no AND transactions.document = lines.document
INNER JOIN status WITH (NOLOCK) ON status.status_code = lines.status_code
INNER JOIN products WITH (NOLOCK) ON lines.product_code = products.product_code
WHERE
	customer = @customer
AND
	facility = @facility
AND
	lines.line_transaction = 'receipt'
AND
	lines.line_status = 'received'
AND
	header_status <> 'received'
AND
	(order LIKE 'xyz%' OR order LIKE 'zyx%')
[b]AND
	CAST(lines.line_code AS DATETIME) BETWEEN @begin_date AND @end_date[/b]

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[ca8msm]

I even know the exact record causing this problem

So what is the value of the "line_code" column for this record?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

>>"Syntax error converting datetime from character string."

check your language settings
what works in US might not work in the UK

there are only 2 formats which will work anywhere regardless
of setting

ISO
yyyymmdd
'20080110'

ISO8601
yyyy-mm-dd Thh:mm:ss:mmm
'2008-01-10T11:14:44.313'


take a look at this

--Not ISO
set dateformat mdy
select convert(datetime, '2007-01-13')

set dateformat ydm
select convert(datetime, '2007-01-13')

--ISO
set dateformat mdy
select convert(datetime, '20070113')

set dateformat ydm
select convert(datetime, '20070113')

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Try this....

[tt][blue]
CAST(Case When IsDate(lines.line_code) = 1 Then lines.line_code else '19000101' End AS DATETIME) BETWEEN @begin_date AND @end_date
[/blue][/tt]

Since you are casting the column to a DateTime, you will never get index seeks. Do a google search on [google]sql sargable[/google] to understand why.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[FatalExceptionError]

So what is the value of the "line_code" column for this record?

Let me clarify this.

The record that is the culprit is invalid (but possibly valid) as it is a name not a date. This record should not be processed since

    lines.line_transaction = 'receipt'
AND
    lines.line_status = 'received'
AND
    header_status <> 'received'
AND
    (order LIKE 'xyz%' OR order LIKE 'zyx%')

will result in false once it gets past customer and facility clauses. This makes me think its not searching arguments the same way on both environments considering the record is there in both. I made the mistake of assuming valid data. I don't want to make the mistake of overlooking different behavior by addressing the symptom and not the problem.

Try this....


CAST(Case When IsDate(lines.line_code) = 1 Then lines.line_code else '19000101' End AS DATETIME) BETWEEN @begin_date AND @end_date


Since you are casting the column to a DateTime, you will never get index seeks. Do a google search on sql sargable to understand why.

Thanks GM I was not even thinking of isdate() or case in where.

I am going to get a refresh on dev to see if I can reproduce the error.

Thanks for your help guys.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't