SQL error: Conversion failed when converting date and/or time from character string.

I have a SQL table called CustomData:

CustomizationId - int
SystemDtTm - datetime
ObjectId - int
Name - varchar(50)
Value - varchar(250)
BusinessDtTm - datetime
Sample DataThe below code fails with the following error message:
Conversion failed when converting date and/or time from character string.

I tried casting cd.Value as Date, but no luck.

declare		@dateGenerated datetime = '8/26/2014'
declare		@customizationIds varchar(8000) = '299'

declare @isCustomization299Allowed BIT = dbo.IsCustomizationAllowed(299, @customizationIds)     
declare @isCustomization451Allowed BIT = dbo.IsCustomizationAllowed(451, @customizationIds)     
declare @isCustomization792Allowed BIT = dbo.IsCustomizationAllowed(792, @customizationIds)   
declare @isCustomization928Allowed BIT = dbo.IsCustomizationAllowed(928, @customizationIds)    

select cd.CustomizationId, p.PremiumFinanceLoanId, p.InsurancePolicyId
from InsurancePolicy p
	join CustomData cd on p.InsurancePolicyId = cd.ObjectId 
where	(@isCustomization299Allowed = 1 and cd.CustomizationId = 299 and Name = 'Policy Number Changed' and cd.Value = @dateGenerated) 
	or 
		(@isCustomization451Allowed = 1 and cd.CustomizationId = 451 and Name = 'Policy Number Changed - Generate new NFP')
	or
		(@isCustomization792Allowed = 1 and cd.CustomizationId = 792 and Name = 'Policy field Changed - Generate new NFP')
	or
		(@isCustomization928Allowed = 1 and cd.CustomizationId = 928 and Name = 'Policy Carrier Changed - Generate new NFP')

Open in new window

LVL 8
pzozulkaAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
My bad, meant to say ISDATE.  So, we're comparing a varchar(250) cd.Value to a date variable @dateGenerated, which will only work by implicit conversion if all values in the varchar are correctly formatted as dates.  To test, ISDATE(any value) = 1

For example..
CREATE TABLE #tmp (value varchar(250))

INSERT INTO #tmp (value) 
VALUES ('2014-01-01'), ('banana'), ('wackadoo'), ('42'), (CAST(GETDATE() as varchar(25))) 

SELECT value, ISDATE(value) 
FROM #tmp

Open in new window


So in your case (sorry again for the typo)..
select * from CustomData WHERE ISDATE(cd.Value) = 0

Open in new window

and if you want to weed these values out of your SQL Statement, add this to the WHERE clause...
WHERE ISDATE(cd.Value) = 1

Open in new window


Potentially stupid question:  Why are we storing dates in a varchar(250) field?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Double-click on the error message, watch the cursor jump to a line in your T-SQL that threw the error, and tell us what line that is.

> and cd.Value = @dateGenerated)
What's the data type of cd.Value, and are there any dates in there that would fail an ISNUMERIC(cd.Value) test?
0
 
pzozulkaAuthor Commented:
It's referring to line 9: select cd.CustomizationId, p.PremiumFinanceLoanId, p.InsurancePolicyId

Just to make sure it's not something in the select clause itself, I change that line to: select 1

And it's still doing the same error message.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pzozulkaAuthor Commented:
What makes this more bizarre is that this same code works on other SQL servers.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<hit refresh, I changed my first comment, probably after you read it>

> and cd.Value = @dateGenerated)
This is the only obvious date comparison I see, so what's the data type of cd.Value, and are there any dates in there that would fail an ISNUMERIC(cd.Value) test?
0
 
pzozulkaAuthor Commented:
Here's the data type of cd.Value: varchar(250)

This column was designed to store temporary data of all types. Numbers, dates, booleans, strings, etc.

I ran the following code, and the ISNUMERIC column returns 0 for all values:

select *, ISNUMERIC(Value) from CustomData
0
 
SimonConnect With a Mentor Commented:
In your sample data the VALUE column contains a mixture of date-like strings, NULLs and non-date strings.

You could do an intial SELECT with a WHERE clause to only include rows with values that can be converted to dates
e.g. WHERE ISDATE(cd.value)

.. and use the resulting derived table in place of your customdata table in the query.

i.e. replacing lines 9-11 of your code listing with
select cd.CustomizationId, p.PremiumFinanceLoanId, p.InsurancePolicyId
from InsurancePolicy p
	join (select * from customdata where ISDATE(value)) cd on p.InsurancePolicyId = cd.ObjectId 

Open in new window

0
 
pzozulkaAuthor Commented:
Jim Horn: The varchar(250) field stores not only dates, but any temp data that needs to be stored there. We use this table to store data from our Web Application that needs to persist beyond normal scope periods.
I tried implementing your suggestion, but it did not help -- see code below.

Results of: select *, ISDATE(cd.Value) from CustomData cd
CustomizationId      SystemDtTm                              ObjectId      Name      Value      BusinessDtTm      (No column name)
299                              2012-06-19 10:14:18.980      82189      Policy Number Changed      12/6/2012      NULL      1
299                              2015-01-23 15:35:32.383      144364      Policy Number Changed      8/26/2014      NULL      1
208                              2012-09-19 08:00:06.580      143165      InstallmentCountSetByUser      true      NULL      0
208                              2012-09-21 09:13:35.830      143176      InstallmentCountSetByUser      true      NULL      0

declare	@dateGenerated datetime = '12/6/2012'
declare	@customizationIds varchar(8000) = '299'

declare @isCustomization299Allowed BIT = dbo.IsCustomizationAllowed(299, @customizationIds)     
declare @isCustomization451Allowed BIT = dbo.IsCustomizationAllowed(451, @customizationIds)

select cd.CustomizationId, p.PremiumFinanceLoanId, p.InsurancePolicyId, cd.*
from InsurancePolicy p join CustomData cd on p.InsurancePolicyId = cd.ObjectId 
where	
		(@isCustomization299Allowed = 1 and cd.CustomizationId = 299 and Name = 'Policy Number Changed' and cd.Value = @dateGenerated and ISDATE(cd.Value) = 1)  
	or 
		(@isCustomization451Allowed = 1 and cd.CustomizationId = 451 and Name = 'Policy Number Changed - Generate new NFP')

Open in new window

0
 
pzozulkaAuthor Commented:
SimonAdept: I don't think your suggestion would work because only clients with customizationID299 (@isCustomization299Allowed) are looking for dates. So I can't eliminate all non-date values from the cd table -- what about clients looking for string data types.
0
 
pzozulkaAuthor Commented:
This works fine:
declare	@dateGenerated datetime = '12/6/2012'
declare	@customizationIds varchar(8000) = '299'

declare @isCustomization299Allowed BIT = dbo.IsCustomizationAllowed(299, @customizationIds)     
declare @isCustomization451Allowed BIT = dbo.IsCustomizationAllowed(451, @customizationIds)

select cd.CustomizationId, p.PremiumFinanceLoanId, p.InsurancePolicyId, cd.*
from InsurancePolicy p join CustomData cd on p.InsurancePolicyId = cd.ObjectId 
where	
		(@isCustomization299Allowed = 1 and cd.CustomizationId = 299 and Name = 'Policy Number Changed' and cd.Value = @dateGenerated and ISDATE(cd.Value) = 1)  
--	or 
--		(@isCustomization451Allowed = 1 and cd.CustomizationId = 451 and Name = 'Policy Number Changed - Generate new NFP')

Open in new window


This causes the error: Conversion failed when converting date and/or time from character string.
declare	@dateGenerated datetime = '12/6/2012'
declare	@customizationIds varchar(8000) = '299'

declare @isCustomization299Allowed BIT = dbo.IsCustomizationAllowed(299, @customizationIds)     
declare @isCustomization451Allowed BIT = dbo.IsCustomizationAllowed(451, @customizationIds)

select cd.CustomizationId, p.PremiumFinanceLoanId, p.InsurancePolicyId, cd.*
from InsurancePolicy p join CustomData cd on p.InsurancePolicyId = cd.ObjectId 
where	
		(@isCustomization299Allowed = 1 and cd.CustomizationId = 299 and Name = 'Policy Number Changed' and cd.Value = @dateGenerated and ISDATE(cd.Value) = 1)  
	or 
		(@isCustomization451Allowed = 1 and cd.CustomizationId = 451 and Name = 'Policy Number Changed - Generate new NFP')

Open in new window

0
 
SimonCommented:
>SimonAdept: I don't think your suggestion would work because only clients with customizationID299 (@isCustomization299Allowed) are looking for dates. So I can't eliminate all non-date values from the cd table -- what about clients looking for string data types.

What I was suggesting was that you only use that query where you have date-strings. Rows containing other types of data would have to be dealt with separately. i.e. CustomerID 299 and customer 451 require different queries.

I can't see how your latest comment (where uncommenting the 'OR' clause results in a date conversion error) makes sense, because the lines you uncomment don't include any implicit or explicit date conversions.

>What makes this more bizarre is that this same code works on other SQL servers.

You say it works on other SQL servers - but is this with the same set of data in the customdata table?
Which MSSQL versions and locales are all the servers you're comparing?
0
 
pzozulkaAuthor Commented:
You say it works on other SQL servers - but is this with the same set of data in the customdata table?
Which MSSQL versions and locales are all the servers you're comparing?

This works in production, and seems to be only an issue in our dev environment. I know the data itself is the same in both dev and production because customizationID 299 will always store dates in the value column (as strings).

Either way, we ran a test by inserting fake values in production environment and in dev, and ran the SELECT query mentioned above, and it only fails in dev. Both versions of SQL are 2014 standard editions.

Microsoft SQL Server Management Studio                                    12.0.2000.8
Microsoft Analysis Services Client Tools                                    12.0.2000.8
Microsoft Data Access Components (MDAC)                                    6.1.7601.17514
Microsoft MSXML                                    3.0 4.0 6.0
Microsoft Internet Explorer                                    9.11.9600.17420
Microsoft .NET Framework                                    4.0.30319.18444
Operating System                                    6.1.7601
0
 
SimonCommented:
Whether or not a given string e.g. '8/26/2014' or '26/8/2014' will cause a conversion error depends on localisation (UK/US date formats) and the SET DATEFORMAT option for example. That's what I meant by the locale settings of the prod/dev servers.
0
 
pzozulkaAuthor Commented:
Oh very interesting point. How do I get the current value of the DATEFORMAT setting so that I can see what it is currently set to in both environments?

Also where do I find the locale settings?
0
 
SimonCommented:
Have a look at this MS support article:
INF: How to Set the Day/Month/Year Date Format in SQL Server
0
 
pzozulkaAuthor Commented:
Thanks SimonAdept, but it seems that these settings are the same across all 3 servers. It was worth a try though.
0
 
pzozulkaAuthor Commented:
So we figured out what the solution is, but I have absolutely no idea why.

The only thing that's different between the below two queries is that I changed the order of the ISDATE(cd.value).
One could attribute this to the fact that SQL creates its own execution plan and the statements on line 10 are evaluated in an order chosen by the query analyzer, but then if that was the case, why would changing the order of ISDATE make a difference?

Fails
declare	@dateGenerated datetime = '12/6/2012'
declare	@customizationIds varchar(8000) = '299'

declare @isCustomization299Allowed BIT = dbo.IsCustomizationAllowed(299, @customizationIds)     
declare @isCustomization451Allowed BIT = dbo.IsCustomizationAllowed(451, @customizationIds)

select cd.CustomizationId, p.PremiumFinanceLoanId, p.InsurancePolicyId, cd.*
from InsurancePolicy p join CustomData cd on p.InsurancePolicyId = cd.ObjectId 
where	
		(@isCustomization299Allowed = 1 and cd.CustomizationId = 299 and Name = 'Policy Number Changed' and cd.Value = @dateGenerated and ISDATE(cd.Value) = 1)  
	or 
		(@isCustomization451Allowed = 1 and cd.CustomizationId = 451 and Name = 'Policy Number Changed - Generate new NFP')

Open in new window


Succeeds
declare	@dateGenerated datetime = '12/6/2012'
declare	@customizationIds varchar(8000) = '299'

declare @isCustomization299Allowed BIT = dbo.IsCustomizationAllowed(299, @customizationIds)     
declare @isCustomization451Allowed BIT = dbo.IsCustomizationAllowed(451, @customizationIds)

select cd.CustomizationId, p.PremiumFinanceLoanId, p.InsurancePolicyId, cd.*
from InsurancePolicy p join CustomData cd on p.InsurancePolicyId = cd.ObjectId 
where	
		(@isCustomization299Allowed = 1 and cd.CustomizationId = 299 and Name = 'Policy Number Changed' and ISDATE(cd.Value) = 1 and cd.Value = @dateGenerated)  
	or 
		(@isCustomization451Allowed = 1 and cd.CustomizationId = 451 and Name = 'Policy Number Changed - Generate new NFP')

Open in new window

0
 
SimonCommented:
Makes some sense, if ISDATE() is evaluated first, it doesn't have to evaluate the "cd.Value = @dateGenerated" for non-dates which was what caused the conversion error.

Interesting that your 3 servers don't all optimise the query in the same way. You'd have to compare the estimated execution plans and actual execution plans. You might also check that auto-create statistics and auto-update statistics are set in the same way for the database on all 3 servers.

Underpinning the issue is your table design, where you have the cd.value column storing dates and other strings, making it impossible for the optimiser to make a consistent judgement on where to place any evaluation of it in the exectuion plan. Creating an intermediate table where you could pull out all the dates for customers like 299 would help.

A good Simple-Talk article here on understanding statistics and query optimisation.

Thanks for posting your question. A good example of how easy it is for a query to pass tests in one environment but fail in another.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.