Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Implicit and Explicit Conversions

By Steve Jones,

In a talk recently with some people I had someone note that the always chose to use explicit conversions on data types to prevent any unforeseen issues. That's what I'd recommend as well. I have seen code in production function for years using implicit conversions, only to start failing when someone finally entered an invalid character in a row.

How does that happen? Usually when someone is using character data types to store data that can be represented as character data,  even though the data must be dealt with in it's native format. An example of this is storing a date as a varchar(10) or sticking numerical quantities in a character field to preserve formatting notations like dollar signs, or commas.

That kind of code can work , pass a QA process, and live for years in a production system. However sooner or later someone will enter data that will break a query and return an error. Depending on your error handling system, this can be problematic to track down because it's very data dependent. The code might work for some data sets but not for others.

The best advice I can give is to store data in the proper data types whenever possible, and use explicit conversions when comparing data that might be of disparate types. Don't always expect '09/01/2001' to compare to getdate(), and don't expect '1' to equal 1 in your code. At some point bad data will get into the system and those comparisons will error out.

Steve Jones


The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

Total article views: 380 | Views in the last 30 days: 5
 
Related Articles
FORUM

Conversion failed when converting datetime from character string - Error....

Conversion failed when converting datetime from character string - Error

FORUM

conversion error

conversion error

FORUM

DATA conversion error

DATA conversion error

FORUM

datetime conversion error

datetime conversion error

FORUM

Error when previewing a report with date parameters 'Conversion failed when converting datetime from character string'

'Conversion failed when converting datetime from character string

Tags
editorial    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones