T-SQL Cast Versus Convert

T-SQL Cast versus Convert

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

EDIT:

As noted by @beruic and @C-F in the comments below, there is possible loss of precision when an implicit conversion is used (that is one where you use neither CAST nor CONVERT). For further information, see CAST and CONVERT and in particular this graphic: SQL Server Data Type Conversion Chart. With this extra information, the original advice still remains the same. Use CAST where possible.

SQL Server cast() vs convert()

From this link :

1. CAST is an ANSI standard while CONVERT is a specific function in the SQL server. There are also differences when it comes to what a particular function can and cannot do.
   For example, a CONVERT function can be used for formatting purposes especially for date/time, data type, and money/data type. Meanwhile, CAST is used to remove or reduce format while still converting. Also, CONVERT can stimulate set date format options while CAST cannot do this function.

2. CAST is also the more portable function of the two. It means that the CAST function can be used by many databases. CAST is also less powerful and less flexible than CONVERT. On the other hand, CONVERT allows more flexibility and is the preferred function to use for data, time values, traditional numbers, and money signifiers. CONVERT is also useful in formatting the data’s format.

3. CAST functions also restore the decimals and numerical values to integers while converting. It also can be used to truncate the decimal portion or value of an integer.

Are CAST and CONVERT the same in SQL?

CAST and CONVERT have similar functionality. 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. Check this blog for examples of using both of those: http://sqltutorials.blogspot.com/2007/06/sql-cast-and-convert.html

Number of classes in large projects

There's really no magic formula for calculating optimal number of classes. The architecture you described above may create a very, very simple airline reservation system. As you continue to refactor, add more features, and accommodate special cases, you could end up with many more classes, e.g., MealPreference, CouponCode, Terminal, Gate, Airline, Baggage, BaggageTransfer, RainCheck, FlightUpgrade etc.

As you should (if you want to be agile), only code exactly what you need at the time, planning ahead for ease of extension. However, any project is going to grow in unanticipated ways over time.

Cast vs Convert SQL Server

There are good reasons why you should not do the second and third options. First consider this:

select cast(floor(cast(GETDATE() as float)) as datetime)

My problem with this is that, although it does work, I cannot find documentation that specifies the behavior. The internal format is not a floating point number; it is two integers, so I find it quite inelegant to go from a date/time to a float. There might exist situations where it is dangerous.

Next:

SELECT CONVERT(VARCHAR,GETDATE(),105)

This version . . . Arggh! It has varchar() with no length argument. Bad habit. Don't do that! It is a confusing problem waiting to happen. So, let's consider:

SELECT CONVERT(VARCHAR(10), GETDATE(), 105)

This is fine if you want a string, but the result is different from your other queries. The equivalent statement is:

SELECT CONVERT(DATE, CONVERT(VARCHAR(10), GETDATE(), 105), 105)

But wait, why go through the intermediate structure of a string? The version you first propose is simpler.

The only downside is that it is not compatible with pre-2008 versions of SQL Server. If you need to be compatible with earlier versions, then I would go for the yucky:

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

This counts the number of days since a fictitious "0" date and then adds them back. If you don't mind typing, the following is pretty much the same thing:

SELECT DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE()), '1900-01-01')

or go through an intermediate string value.

The reason SQL Server has several work-arounds for this functionality is because the functionality is very useful, but the date date type was only introduced in SQL Server 2008.

Whats is the difference between using Convert and Cast in a Select statement

No, performance is not a issue.(As far as your question describes both can be used and there is no difference between them but as martin said statement using CAST will cast to varchar(30) as it is it's default length)

CAST is an ANSI SQL-92

CONVERT is specific to SQL Server

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.

Try this

SELECT DISTINCT CONVERT(VARCHAR(MAX), GETDATE(),108)
--and the other is saying to use

select distinct cast(GETDATE() as VARCHAR(MAX))

The difference between Parse and Convert in SQL Server

The PARSE function is new in SQL Server 2012 and uses the .NET CLR - It's not native T-SQL, while the CONVERT function is native T-SQL. There might be some performance overhead when using the PARSE function and it also depends on the presence of .NET CLR on your database server.

A quote from http://sqlhints.com/tag/convert-vs-parse/ :

PARSE function will successfully converts the string ‘Saturday, 08
June 2013’ to date time, but the CONVERT function fails to convert the
same value. That is PARSE function tries it’s best to convert the
input string value to the requested type, but CONVERT function
requires the input string to be exact format no variations allowed.

Check out the article for more information:
http://sqlhints.com/tag/convert-vs-parse/

T-SQL Query using WHERE clause with CONVERT or CAST

Please make sure there is no data returning from:

select * from [dbo].[GLSUM] where [Period] = 'R2W274'

Adding a try/catch block like that will help:

BEGIN TRY  
    -- Generate divide-by-zero error.  
    your select query comes here.  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    print 'error';
END CATCH;

Try/catch block reference: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

What I would do is to eliminate such data inside where clause:

WHERE gl.ENTITYID IN (SELECT entityId FROM @listOfIds)
        AND gl.BASIS = 'A'
        AND gl.ACTIVITY != 0
        AND gl.[PERIOD] not like '%[^0-9]%'
        AND CONVERT(INT,gl.[PERIOD]) >= @intStartDate

Edit: I was expecting the change in where clause would fix it, surprised why it is not working. Please make sure you added the filter before the one including the conversion. Maybe it would be safer to use a temptable like:

select * 
into #temp_glsum
from [dbo].[GLSUM] gl
WHERE gl.[PERIOD] not like '%[^0-9]%'

and use #temp_glsum in your main query instead of [dbo].[GLSUM]

SQL CAST or Convert Function

Try this

    case when [ A 2 cost] = ' £-   ' 
then 0.0 else cast([ A 2 cost] as decimal(5,2)) end as Costing

if the other columns contains £ in the beginning you need to clean it:

    case when [ A 2 cost] = ' £-   ' 
then 0.0 else cast(REPLACE([ A 2 cost],'£','') as decimal(5,2)) end as Costing

---

Related Topics