Best of the SQL Server DBAs I apperceive use the CONVERT to change abstracts types in their SQL code. However, this is not an ANSI accepted and banned the portability. While I rarely see databases affective from SQL Server to some added RDBMS, I accept apparent it happen. Also, as added SQL Server databases grow, there will be added and added DBAs that appear from added articles to assignment with SQL Server. These DBAs will acceptable address SQL cipher that is added SQL-92 accepted and will not accommodate the T-SQL bequest cipher words.
In adjustment to ensure your cipher can be maintained by others as able-bodied body your ANSI skills, I acclaim alpha to catechumen some of your cipher to attach to SQL-92 standards. One SQL action that you should become accustomed with is the CAST function.
Essentially CAST performs the aforementioned action as CONVERT, namely alteration abstracts from one abstracts blazon to another. The basal architecture of the CAST action is as follows (from the latest Books Online in SQL Server 2000:
CAST and CONVERT Explicitly converts an announcement of one abstracts blazon to another. CAST and CONVERT accommodate agnate functionality Here is the syntax :
SyntaxUsing CAST: CAST ( announcement AS data_type)Using CONVERT: CONVERT(data_type[ ( length)], announcement [,style] )Arguments announcement Is any accurate Microsoft® SQL Server(tm) expression.data_type Is the ambition system-supplied abstracts type, including bigint and sql_variant. User-defined abstracts types cannot be used.
As you may accept noticed, the CONVERT action has added options than CAST. There are instances area CONVERT is needed, but in accepted according to Books Online:
“CAST is based on the SQL-92 accepted and is adopted over CONVERT.” In addition, if you advertence the about-face blueprint that is supplied in Books Online, you will see the afterward chart:
If you apprehension the fourth footnote, it mentions that there are a few cases area CONVERT can account a accident of attention and that CAST is required. These cases are the four cases for decimal/numeric to decimal/numeric conversions.
At Tech Ed 2000, all of the demonstrations accustomed apropos the new sql_variant abstracts blazon and books online authenticate the claim to aces a abstracts blazon back assuming operations on abstracts of this abstracts type. Each of these demonstrations was accustomed application CAST as the archetype function. This acumen abandoned is account acquirements to use CAST back best sample cipher from the SQL aggregation in Redmond will acceptable use this function.
When you charge a appearance for the new abstracts type, again CONVERT is appropriate back CAST does not abutment this functionality. If you analysis the Books Online CONVERT page, you will see the afterward account of styles:
* The absence ethics (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) consistently acknowledgment the aeon (yyyy). ** Input back converting todatetime; Output back converting to appearance data.
Also, in the Oracle abstracts about-face area of Books Online, there are two places area CONVERT is apparent as an archetype rather than CAST. 1.Conversions of date to burn (because of styles) 2.Conversions of Binary to Hex
Some examples of application CAST are apparent below. These are mainly simple abstracts blazon conversions that I accept acclimated in the past.
1. Catechumen an appearance cord into a integer. Keep in apperception the cord charge be alone letters.
2. Catechumen an appearance cord into a date. Keep in apperception the cord charge be in a apparent date format.
3. Catechumen a float appearance cord into a string.
There are abounding means in which CAST can be acclimated and abounding accomplished examples in Books Online. You should analysis there for added examples. If there are specifics that you would like to see, amuse email me and I will add them to this article.
As consistently I acceptable acknowledgment on this commodity application the “Your Opinion” on below. Amuse also amount this article.
Return to Steve Jones Home
10 Reasons You Should Fall In Love With Ansi Standards Chart | Ansi Standards Chart – ansi standards chart
| Pleasant to our website, on this time period I’m going to provide you with about ansi standards chart