Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Implicit and Explicit Conversions


Implicit and Explicit Conversions

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36018 Visits: 18731
Comments posted to this topic are about the item Implicit and Explicit Conversions

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Nakul Vachhrajani
Nakul Vachhrajani
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1823 Visits: 2126
I couldn't agree more. Whenever I carry out code reviews, especially for junior members of the team, I typically find these to be the most common errors they make. I have seen people using VARCHAR for storing everything (dates, money, floats)! Their excuse? Globalization/Internationalization.
We have a variety of data-types that can satisfy almost every computing and storage need - whenever a system is designed, time should be spent arguing the use of one data type of the other. Once the decision has been made, the code should not assume that the field will contain only one data pattern - unless the code is explicitly taking care of the same (eg. storing all dates in UTC, and then the representation is handled by the UI). Either way, exlicit checks & conversions should always be made.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
montgark
montgark
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 95
I also agree. Absolutely. In my organization this is a pandemic. And it strikes specially with dates. Most dates are stored as numeric(8,0) in the central DB2 database, coming directly from fields with PIC(99999999) from transactional files. In SQL Server we need them to be real dates, because the datamart needs date arithmetics (and because that is the correct way to store a date value!). So we have to explicitly make every translation and to correct the many errors we find, filtering non-valid values at ETL time. Much of this effort could be saved by just defining DATE columns in the original database. We try to evangelize on this. But, as to date, even new tables are still created with numeric(8,0) columns for date values. "For agility purposes", they say. (????)
OCTom
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2813 Visits: 4152
BITD(back-in-the-day), there were fewer options for storing data. Dates were generally stored either as 6-digits or 3, 2-digit fields. Fields were not changed when that data was ported to a SQL DB.

However, anything more recent should not be storing data incorrectly. A date should be a date or datetime data type. Editing should occur in the UI or the reports and not the database. Storing money with the currency sign the database is not necessary. Data warehouses may do that, though.

The closer you are to matching the datas intended use and its type, the better off you will be. It takes more time but it's worth it in the long run.
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4574 Visits: 9495
The representation of dates as character strings in a database (what I'll call a "VarDate") is one of the leading causes of business intelligence disasters.
The day will come when another peripheral application or ETL process will start inserting into or querying from your database, and they won't follow (or be aware of) the assumption for how the "VarDate" should be formatted.
Other RDBMS like Oracle follow different rules about how they implicitly convert data types. For example, SQL Server will reliably convert YYYY-MM-DD or YYYYMMDD to a Date, but Oracle will only implicitly convert a char value in the format 'DD-MMM-YYYY' or 'DD-MMM-YY'.
An even worse scenario than implicit conversion that throws an error is an implicit conversion that works, but works in a way that you didn't expect. The application appears to be running normally, but you discover months down the road that payroll or invoices submitted to clients been totaled incorrectly or duplicate records have been entered into tables where a VarDate column is part of the primary key.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
richardd
richardd
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3041 Visits: 641
Nakul Vachhrajani (3/16/2011)
I have seen people using VARCHAR for storing everything (dates, money, floats)! Their excuse? Globalization/Internationalization.


How can anyone claim that storing a date as the string "09/01/2001" is better for globalization? Crazy If you're using a US culture, that's "1st September 2001"; in most other cultures, it's "9th January 2001".



Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4574 Visits: 9495
richardd (3/17/2011)
Nakul Vachhrajani (3/16/2011)
I have seen people using VARCHAR for storing everything (dates, money, floats)! Their excuse? Globalization/Internationalization.

How can anyone claim that storing a date as the string "09/01/2001" is better for globalization? Crazy If you're using a US culture, that's "1st September 2001"; in most other cultures, it's "9th January 2001".

What you can expect in this situation is that users in the Asian center will enter 9th January 2001 as '09/01/2001' and users in the US will enter 1st September 2001 as '09/01/2001', and of course management will complain that IT can never get the month end reporting working right.

If an application must use VarDate columns, then they should at least code it using ISO standard format YYYYMMDD and use a check constraint to enforce proper formatting like so:
create table MyTable
(
MyDate char(8) not null check (MyDate like '[1-2][09][0-9][0-9][01][0-9][0123][0-9]')
)


The advantage of the ISO format over 'MM/DD/YYYY' or 'MMM DD, YYYY' is that with 'YYYYMMDD' there is less ambiguity, and it will index, sort, and compare (<, >, between) correctly without requiring any datatype conversion to Date.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6230 Visits: 10398
Two comments:
1. If you're going to store dates in a varchar, at least store it in YYYYMMDD format.
2. Implicit conversions can also cause poor performing queries. Say you have a six-digit char column, zero-prefilled so that you have data like "001843". If you search for 1843, it will find the match, but it will also convert all of that column to an int. Even if you have an index on that column, it now won't be used.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Mike B in AK
Mike B in AK
SSC-Addicted
SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)SSC-Addicted (407 reputation)

Group: General Forum Members
Points: 407 Visits: 633
Interestingly, I inherited a process where the previous person stored integer values in a varchar(3) field and did poor quality checks on the data coming in. The people doing data entry often misread a zero for the letter D.

I was tasked with trying to code for some statistical samplings. The problem was, I could not match the previous statistics. Most years were very close and could mostly be attributed to different rounding methods between SQL Server and the stats package they had been using. One year of data was way off. This is when I found that I had two entries in the integer holding Varchar(2) field that were entered as D0. I had used logic to make the D a zero. However, the old stats package had apparently been treating it as hex and converted it to 208.

It was near impossible to convince the data owners that their data quality was responsible and that their previous reports were in error.

So, I guess my point is that, aside from poor QA on the data entry, that we do not always know how other tools will handle data when we do poor data typing.

By the way, it took 2 other analysts and a statistician to convince the data owners their data was of poor quality.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36018 Visits: 18731
WayneS (3/17/2011)
2. Implicit conversions can also cause poor performing queries.


Especially in SQL CLR. If you pass in the wrong types for conversions, you might end up with multiple conversions of the same value back and forth.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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