Solved

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

Posted on 2015-01-23
18
419 Views
Last Modified: 2015-01-26
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

0
Comment
Question by:pzozulka
  • 10
  • 5
  • 3
18 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40567587
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40567590
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40567591
What makes this more bizarre is that this same code works on other SQL servers.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40567592
<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
 
LVL 8

Author Comment

by:pzozulka
ID: 40567593
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40567606
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
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 250 total points
ID: 40567612
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40568551
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40568555
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 8

Author Comment

by:pzozulka
ID: 40568564
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
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40568614
>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
 
LVL 8

Author Comment

by:pzozulka
ID: 40568648
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
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40568654
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40568687
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
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40569108
Have a look at this MS support article:
INF: How to Set the Day/Month/Year Date Format in SQL Server
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40570761
Thanks SimonAdept, but it seems that these settings are the same across all 3 servers. It was worth a try though.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40570769
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
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40570911
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now