Comparing CONVERT and CAST in SQL server

Comparing CONVERT and CAST in SQL server

This tip originally appeared on SearchWin2000.com, a sister site of SearchSmallBizIT.com.

SQL Server programmers can choose between two functions in SQL Server 7 and 2000 for converting expressions from one type to another. In many cases

    Requires Free Membership to View

    Login

    When you register you’ll also receive the latest news, advice and technical tips designed specifically for midmarket IT leaders like yourself. Our award-winning editorial team will give you immediate access to emerging business and technology trends.

    Scot Petersen, Editorial Director, SearchCIO-Midmarket

    By submitting your registration information to SearchCIO-MidMarket.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchCIO-MidMarket.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

there will be a need within a stored procedure or other routine to convert data from, say, a datetime type to a varchar type; CONVERT and CAST are used for such things.

Because SQL Server provides both functions, there may be some confusion about which is best to use and under what circumstances. CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers and monetary signifiers.

CAST is the more ANSI-standard of the two functions, meaning that while it's more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it's also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it's best to use CAST first, unless there is some specific thing that only CONVERT can provide in the work you're doing.

CAST and CONVERT can also be used in conjunction with each other to achieve certain effects. For instance, a typical way to produce a char variable with the current date would be to use:

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102)

(The 102 indicates that the ANSI date format, yy.mm.dd, is to be used.)

However, if you wanted to cast this variable explicitly as a datetime or smalldatetime value for compatibility in a specific database column, you could use:

SELECT CAST(CONVERT(CHAR(10),CURRENT_TIMESTAMP,102) AS DATETIME

This would return the value yy.mm.dd 00:00:00 (i.e., 12:00AM as the timestamp; the time information from CURRENT_TIMESTAMP would be discarded).

Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!

Do you have comments on this tip? Let us know.


This was first published in December 2004

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top