Click here to monitor SSC
  • Av rating:
  • Total votes: 73
  • Total comments: 9
Robert Sheldon

Data Conversion in SQL Server

06 January 2011

Most of the time, you do not have to worry about implicit conversion in SQL expressions, or when assigning a value to a column. Just occasionally, though, you'll find that data gets truncated, queries run slowly, or comparisons just seem plain wrong. Robert explains why you sometimes need to be very careful if you mix data types when manipulating values.

When working with data in a SQL Server database, you might find that some of that data needs to be converted from one data type to another. For example, suppose you need to compare two numbers, one configured with a numeric data type and one configured with a string data type. To perform the comparison, one of those values will have to be converted to the other’s data type.

SQL Server supports two types of data type conversion: implicit and explicit. Implicit means that the database engine will convert the data type automatically, a process invisible to the user. Explicit means that you must specify how the data type should be converted. If you don’t specify how SQL Server should convert the data types to do what you want (explicitly), it will try to guess your intentions (implicitly).

In this article, I introduce you to both types of data type conversions and provide examples that demonstrate how they work, with a few warnings about the sort of thing that can go wrong with implicit conversions. The examples are written against the AdventureWorks2008 sample database on a local instance of SQL Server 2008. A couple of examples use SQL Server 2008 syntax but you should have no problem running the others against the AdventureWorks database on an instance of SQL Server 2005.

NOTE: Data type conversion can also occur when an outside application retrieves data from a SQL Server database. However, this article is concerned only with data conversion between SQL Server objects.

Implicit Data Conversions

When data needs to be converted from one data type to another, SQL Server can often convert the value automatically (implicitly). For example, suppose you want to add two integers together and they’re configured with different data types, as in the following example:

DECLARE @Val1 CHAR(2) = '2';

DECLARE @Val2 INT = 3;

SELECT

  (@Val1 + @Val2) AS Total,

CASE

    WHEN ISNUMERIC(@Val1 + @Val2) = 0 THEN 'No'

    WHEN ISNUMERIC(@Val1 + @Val2) = 1 THEN 'Yes'

END AS IsNumber;

In this example, I’ve configured the @Val1 variable with the CHAR data type and assigned a value of 2. I’ve configured the @Val2 variable with the INT data type and assigned a value of 3. When I add these values together, SQL Server automatically converts the @Val1 variable to an INT data type and produces a numeric sum, which I confirm by using the ISNUMERIC function. If the function returns a 0, the sum is not numeric. If the function returns a 1, it is numeric. The following table shows the query results, which indicate that the sum is a numeric value:

Total

IsNumber

5

Yes

If I were to change the second line to...

DECLARE @Val2 Char(6) = 'please';

...then I’d get the result...

Total

IsNumber

2 please

No

If you mix data types such as INT and VARCHAR, SQL Server will always attempt to convert everything to the one that has the highest precedence. This is why you will sometimes get errors if you try to use implicit conversions to add a number to a string.

SQL Server supports a significant number of implicit data conversions that let you easily compare, combine, or move data. Data can be converted within a particular data type grouping, such as one type of string value to another (for instance, NCHAR to NVARCHAR) or between type groupings, such as a numeric value to a string value (for instance, INT to VARCHAR).

For the most part, SQL Server handles implicit conversions seamlessly, as in the first example above. However, you should be aware whenever the database engine performs implicit conversions because some conversions can lead to unexpected results.

For example, when SQL Server converts a string value of one size to a data type of a smaller size, the database engine truncates the value. In the following set of statements, I declare VARCHAR and CHAR variables and then assign values to them:

DECLARE @Val3 VARCHAR(4);

DECLARE @Val4 CHAR(2);

SET @Val3 = 'abcd';

SET @Val4 = @Val3

SELECT

  @Val3 AS Value3,

  @Val4 AS Value4;

As you can see, I’ve configured @Var3 with the VARCHAR(4) data type and @Var4 with the CHAR(2) data type. After I assign the string abcd to @Val3, I then set the value of @Val4 to equal @Val3. Because @Val3 is made up of four characters and @Val4 can handle only two characters, the string is truncated, as the following results show:

Value3

Value4

abcd

ab

If you assign a VARCHAR value to a table that has insufficient space to contain it, using an INSERT or UPDATE statement, you would get a ‘truncation’ error. However, if you assign to a local variable or the parameter to a routine, you don’t: the value just gets truncated.

Another example of behavior to be aware of when implicit conversions are taking place is when you convert a number from a DECIMAL data type to an INT, as I do in the following example:

DECLARE @Val5 DECIMAL(7,2);

DECLARE @Val6 INT;

SET @Val5 = 12345.67;

SET @Val6 = @Val5

SELECT

  @Val5 AS Value5,

  @Val6 AS Value6;

Notice that the @Val5 variable has been assigned a value of 12345.67. I then assign that variable to @Var6. However, because @Var6 is configured with an INT data type, it can handle only whole numbers, so the numbers after the decimal are truncated, as you can see in the following results:

Value5

Value6

12345.67

12345

The dangers of any conversion can also be graphically illustrated by this code.

DECLARE @Val1 INT  = 1000000000

DECLARE @Val2 INT  = 1000000028

DECLARE @Val3 REAL

 

Set @Val3=@Val2

SELECT

 CASE

       WHEN @Val3 = @Val1

       THEN 'Yes! These numbers are equal'

       ELSE 'No!These numbers are''nt equal'

 END AS [Equality Test],

 CONVERT(INT, @Val3) as [Val3 as an int], @Val3 as [Val3]

Which gives the surprising result:

Equality Test

Val3 as an int

Val3

Yes! These numbers are equal

1000000000

1E+09

From what I’ve told you, you’ll see that the REAL value has insufficient precision to hold the INT, and the original value has been rounded down.

You should also be aware of the impact that implicit conversions can have on a query’s performance. To demonstrate what I mean, I’ve created and populated the following table in the AdventureWorks2008 database:

USE AdventureWorks2008;

 

IF OBJECT_ID ('ProductInfo', 'U') IS NOT NULL

DROP TABLE ProductInfo;

 

CREATE TABLE ProductInfo

(

  ProductID NVARCHAR(10) NOT NULL PRIMARY KEY,

  ProductName NVARCHAR(50) NOT NULL

);

 

INSERT INTO ProductInfo

SELECT ProductID, Name

FROM Production.Product;

As you can see, the table includes a primary key configured with the NVARCHAR data type. Because the ProductID column is the primary key, it will automatically be configured with a clustered index. Next, I set the statistics IO to on so I can view information about disk activity:

SET STATISTICS IO ON;

Then I run the following SELECT statement to retrieve product information for product 350:

SELECT ProductID, ProductName

FROM ProductInfo

WHERE ProductID = 350;

Because statistics IO is turned on, my results include the following information:

Table 'ProductInfo'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Two important items to notice is that the query performed a scan and that it took six logical reads to retrieve the data. Because my WHERE clause specified a value in the primary key column as part of the search condition, I would have expected an index seek to be performed, rather than I scan. To learn more about the scan, I viewed the query’s execution plan, which is shown in Figure 1.

Figure 1: Execution Plan Showing an Index Scan

As the figure confirms, the database engine performed a scan, rather than a seek. Figure 2 shows the details of that scan (accessed by hovering the mouse over the scan icon).

Figure 2: Detail of the Index Scan Operation

Notice that in the Predicate section, the CONVERT_IMPLICIT function is being used to convert the values in the ProductID column in order to compare them to the value of 350 (represented by @1) I passed into the WHERE clause. The reason that the data is being implicitly converted is because I passed the 350 in as an integer value, not a string value, so SQL Server is converting all the ProductID values to integers in order to perform the comparisons.

Because there are relatively few rows in the ProductInfo table, performance is not much of a consideration in this instance. But if your table contains millions of rows, you’re talking about a serious hit on performance. The way to get around this, of course, is to pass in the 350 argument as a string, as I’ve done in the following example:

SELECT ProductID, ProductName

FROM ProductInfo

WHERE ProductID = '350';

Once again, the statement returns the product information and the statistics IO data, as shown in the following results:

Table 'ProductInfo'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Notice that no scans have been performed this time and only two logical reads are indicated. Again, if this were a table with millions of rows, this would represent a significant improvement in performance because the query would be performing an index seek, as confirmed in the execution plan (shown in Figure 3).

Figure 3: Execution Plan Showing an Index Seek

Now the index is being properly used to locate the record. And if you refer to Figure 4, you’ll see that the values in the ProductID column are no longer being implicitly converted before being compared to the 350 specified in the search condition.

Figure 4: Detail of the Index Seek Operation

As this example demonstrates, you need to be aware of how performance can be affected by implicit conversions, just like you need to be aware of any types of implicit conversions being conducted by the database engine. For that reason, you’ll often want to explicitly convert your data so you can control the impact of that conversion.

Explicit Data Conversions

Explicit conversions let you exercise more control over your data type conversions whenever you compare, combine, or move data from one database object to another. To support explicit conversions, SQL Server provides two important functions: CAST and CONVERT. The functions are similar in their ability to convert data. However, the CAST function conforms to ISO specifications, which makes it more portable. The CONVERT function, on the other hand, is specific to SQL Server, but it supports additional functionality that lets you better control the format of some types of data.

Working with the CAST Function

The CAST function lets you convert an expression, such as a value retrieved from a column or variable, to a different data type. The function takes the following syntax:

CAST (expression AS data_type [ (length ) ] )

As the syntax indicates, you must first specify the CAST keyword and then the necessary arguments, enclosed in the parentheses. The first argument is the expression that you want to convert. This is followed by the AS keyword and then by the data type that you want to convert the expression to. When applicable, you can also specify a length for your data type.

Let’s look at an example that demonstrates how this works. In the following SELECT statement, I retrieve data from the Production.Product table in the AdventureWorks2008 database:

SELECT

  Name AS ProductName,

  CAST(Name AS BINARY(30)) AS BinaryName

FROM

  Production.Product

WHERE

  ProductID = 720;

In this statement, I use the CAST function to explicitly convert the value in the Name column. The column is configured with the VARCHAR data type, and I’m converting the value to the BINARY(30) data type. The following table shows the results returned by the query:

ProductName

BinaryName

HL Road Frame - Red, 52

0x48004C00200052006F006100640020004600720061006D00650020002D00

As you can see, SQL Server has converted the data to a binary value. One issue worth noting, however, is what happens to a value when you convert it to a binary data type of a different length than the original. For example, if you convert a string value to a binary value that is larger or smaller, SQL Server pads or truncates the data on the right side of the value. For other data types, SQL Server pads or truncates the value on the left.

Low let’s look at converting data to a string value. In the next example, I convert a UNIQUEIDENTIFIER value and DATETIME value to VARCHAR data:

SELECT

  Name AS ProductName,

  CAST(rowguid AS VARCHAR(36)) AS RowGuid,

  CAST(ModifiedDate AS VARCHAR) AS ModifiedDate

FROM

  Production.Product

WHERE

  ProductID = 720;

In the first CAST function, I include the length (36) with the VARCHAR data type to accommodate the size of the UNIQUEIDENTIFIER column. However, in the second instance of CAST, I do not include the length. It’s not necessary in this case because SQL Server automatically returns the DATETIME data in the predefined format, as shown in the following query results:

ProductName

RowGuid

ModifiedDate

HL Road Frame - Red, 52

FCFEA68F-310E-4E6E-9F99-BB17D011EBAE

Mar 11 2004 10:01AM

Notice that the UNIQUEIDENTIFIER value from the rowguid column is returned as it is stored in the source column. However, because this column is configured with the UNIQUEIDENTIFIER data type, if I had specified a length shorter than 36, the SELECT statement would have generated an error.

In the next example, I convert the value in the ListPrice column, configured with the MONEY data type, to several different data types:

SELECT

  Name AS ProductName,

  CAST(ListPrice AS VARCHAR(10)) AS StringPrice,

  CAST(ListPrice AS DECIMAL(10,4)) AS DecimalPrice,

  CAST(ListPrice AS INT) AS IntPrice

FROM

  Production.Product

WHERE

  ProductID = 720;

The original ListPrice value for this product is 1431.50. In the first instance of the CAST function, I convert the ListPrice value to VARCHAR. In the second instance, I convert the value to DECIMAL. And in the third, I convert the value to INT. The following table shows the results returned by the SELECT statement:

ProductName

StringPrice

DecimalPrice

IntPrice

HL Road Frame - Red, 52

1431.50

1431.5000

1432

As you can see, the conversion to VARCHAR is fairly straightforward; the original value is returned as a string. The DECIMAL value is a little different. Because I define the scale as 4, SQL Server adds two zeros to the amount to conform to the specified scale. However, when I convert the value to INT, the database engine rounds the value to an integer, in this case 1432.

As this example demonstrates, when you convert numeric data, your values can sometimes be changed. For example, converting DECIMAL data to FLOAT or REAL can result in the loss of precision.

In the following SELECT statement, I use the CAST function to convert a SMALLINT value to VARCHAR (the ReorderPoint column), a BIT value to TINYINT (the MakeFlag column), and DECIMAL value to FLOAT (the Weight column):

SELECT

  Name AS ProductName,

  CAST(ReorderPoint AS VARCHAR(10)) AS ReorderPoint,

  CAST(MakeFlag AS TINYINT) AS MakeFlag,

  CAST(Weight AS FLOAT) AS ProductWeight

FROM

  Production.Product

WHERE

  ProductID = 720;

The following table shows the results returned by the SELECT statement:

ProductName

ReorderPoint

MakeFlag

ProductWeight

HL Road Frame - Red, 52

375

1

2.2

As you’d expect, the VARCHAR conversion returns the original value as a string. In addition, the TINYINT conversion returns the value as an integer. However, because the MakeFlag column is a BIT column, the value returned will be only 0 or 1. (Any nonzero value is promoted to 1.) The FLOAT conversion returns a slightly different value than the original. The original value is 2.20, but the conversion to FLOAT drops the final 0.

Working with the CONVERT Function

Like the CAST function, the CONVERT function lets you convert an expression to a different data type. The function takes the following syntax:

CONVERT (data_type [ (length ) ] ,expression [ ,style ] )

The syntax elements in the CONVERT function are in a different order from the CAST function, but are essentially the same. You specify the data type, along with the length, if necessary, followed by the expression to be converted. (There is no AS keyword in CONVERT.) In addition, the CONVERT function supports one other argument that CAST does not—style. The style argument is an integer that represents a predefined format. The styles are specific to the data type that is being converted. You can find a list of supported styles for each data type in the topic “CAST and CONVERT (Transact-SQL)” in SQL Server Books Online.

NOTE: Not all data types support format styles, in which case, you might consider using the CAST function instead because it conforms to ISO specifications.

For example, when you convert a DATETIME value to VARCHAR, there are numerous styles that you can choose from. In the following SELECT statement, I convert the value from the SellStartDate column (configured with the DATETIME data type) to three different styles supported for the DATETIME data type:

SELECT

  Name AS ProductName,

  CONVERT(VARCHAR(20), SellStartDate, 100) AS StartDate100,

  CONVERT(VARCHAR(10), SellStartDate, 101) AS StartDate101,

  CONVERT(VARCHAR(20), SellStartDate, 102) AS StartDate102

FROM

  Production.Product

WHERE

  ProductID = 720;

By themselves, these style numbers mean nothing. It’s only when they’re passed as an argument into the CONVERT function along with a specific data type do they become meaningful. The following table shows the results returned by the SELECT statement:

ProductName

StartDate100

StartDate101

StartDate102

HL Road Frame - Red, 52

Jul  1 2001 12:00AM

07/01/2001

2001.07.01

As you can see, each style displays the date in a different format. There are about 21 styles to choose from, so you should usually be able to come up with a format that fits your needs. Here is a chart of the various formats.

Name

2-digit year

example

4-digit year

Example

Default

-

-

100

Jan  4 2011 11:27AM

USA

1

01/04/11

101

01/04/2011

ANSI

2

11.01.04

102

2011.01.04

British/French

3

04/01/11

103

04/01/2011

German

4

04.01.11

104

04.01.2011

Italian

5

04-01-11

105

04-01-2011

dd mon yy

6

04 Jan 11

106

04 Jan 2011

Mon dd, yy

7

Jan 04, 11

107

Jan 04, 2011

hh:mm:ss

8

11:27:09

108

11:27:09

Default + milliseconds

-

-

109

Jan  4 2011 11:27:09:907AM

USA

10

01-04-11

110

01-04-2011

JAPAN

11

11/01/04

111

2011/01/04

ISO

12

110104

112

20110104

Europe default(24h) + milliseconds

-

-

113

04 Jan 2011 11:27:09:907

hh:mi:ss:mmm (24h)

14

11:27:09:907

114

11:27:09:907

ODBC canonical (24h)

-

-

120

2011-01-04 11:27:09

ODBC canonical (24h)+ milliseconds

-

-

121

2011-01-04 11:27:09.907

ISO8601

-

-

126

2011-01-04T11:27:09.907

ISO8601 with time zone

-

-

127

2011-01-04T11:27:09.907

Hijri

-

-

130

29 محرم 1432 11:27:09:907AM

Hijri

-

-

131

29/01/1432 11:27:09:907AM

A rather different danger of relying on implicit conversions for dates can be shown in this example.

DECLARE @Dates TABLE(

        Date_ID INT IDENTITY(1,1),

       TheDate datetime)

/* insert some string values into a column whose datatype is a DateTime */

INSERT INTO @Dates(TheDate)

VALUES ('1 Jan 2011'),

       ('2 Jan 2011'),

       ('3 Jan 2011'),

       ('4 Jan 2011'),

       ('')

 

SELECT * FROM @Dates

The following results show how SQL Server converts these values:

Date_ID

TheDate

1

2011-01-01 00:00:00.000

2

2011-01-02 00:00:00.000

3

2011-01-03 00:00:00.000

4

2011-01-04 00:00:00.000

5

1900-01-01 00:00:00.000

You probably intended that last date to be a null, or unknown, date in the original table, but it has been translated to the start of the twentieth century.

A better approach would involve at least a check that the date was valid (and that the language setting was correct.)

DECLARE @Dates TABLE(

        Date_ID INT IDENTITY(1,1),

       TheDate datetime)

/* insert some string values into a column whose datatype is a DateTime */

INSERT INTO @Dates(TheDate)

SELECT CASE WHEN ISDATE(VarcharDate)=0

            THEN NULL

            ELSE VarcharDate END

FROM

(VALUES ('1 Jan 2011'),

        ('2 Jan 2011'),

        ('3 Jan 2011'),

        ('4 Jan 2011'),

        ('')) AS X (VarcharDate);

The CONVERT function is also handy when you want to convert a MONEY value to a string value. In the following example, I use the CONVERT function to convert a variable value configured with the MONEY data type:

DECLARE @ProductCost MONEY;

SET @ProductCost = 123456789.12667;

SELECT

  @ProductCost AS ProductCost,

  CONVERT(VARCHAR(15), @ProductCost, 1) AS Cost1,

  CONVERT(VARCHAR(15), @ProductCost, 2) AS Cost2;

In this case, two of the supported arguments are 1 and 2. The SELECT statement returns the following results:

ProductCost

Cost1

Cost2

123456789.1234

123,456,789.13

123456789.1267

As you can see, style 1 adds commas to the amount but rounds the value to two decimal places. Style 2, on the other hand, does not add commas and rounds value to four decimal places.

A Good Start

The rules that govern data conversion in SQL Server—whether implicit or explicit—can be somewhat complicated. Not only are there issues of performance and truncation to take into consideration, but also the fact that not all data types can be converted from one to another. In addition, only some types can be implicitly converted by the database engine. The others must be explicitly converted.

At best, what I’ve provided here is an introduction to data type conversion. You should verify the implications of your conversions whenever you’re uncertain of the impact of what you’re doing. Two good topics worth referencing in SQL Server Books Online are “Data Type Conversion (Database Engine)” and “CAST and CONVERT (SQL Server).” Above all, you should be aware of all conversions that occur when moving, copying, or comparing data in SQL Server.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 73 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: its very good
Posted by: Anonymous (not signed in)
Posted on: Friday, January 07, 2011 at 4:07 AM
Message: its very good

Subject: Data conversions...
Posted by: Ian Stirk (not signed in)
Posted on: Monday, January 10, 2011 at 1:49 AM
Message: Hi,

Nice article.

You can discover which columns in your tables have mismatched datatypes by using the utility given in this article: http://www.sqlservercentral.com/articles/Administration/65138/

Additionally, you can use the utility in the following article to search your cached plans for implicit data conversions. http://www.sqlservercentral.com/articles/Performance+Tuning/66729/


Thanks
Ian Stirk

www.manning.com/stirk


Subject: Much bad information here...
Posted by: Anonymous (not signed in)
Posted on: Monday, January 10, 2011 at 12:47 PM
Message: In your third paragraph, you write "you should have no problem running [these examples] ... on an instance of SQL Server 2005." That's incorrect, of course, as some of your examples use compound assignment or table value constructors, neither of which is available in SQL Server 2005. (We've amended the statement to avoid confusion. Ed:)

Your first example (under 'Implicit Data Conversions') has a misleading use of the ISNUMERIC function. That function does not, as you imply, return any information about the data type of an expression. It instead returns whether an expression can evaluate to a valid numeric data type. Changing your example slightly:

DECLARE @Val1 char = '2';
DECLARE @Val2 char = 3;
SELECT @Val1 + @Val2 AS Total,
CASE
WHEN ISNUMERIC(@Val1 + @Val2) = 0 THEN 'No'
WHEN ISNUMERIC(@Val1 + @Val2) = 1 THEN 'Yes'
END AS IsNumber;

Note that I omitted the explicit length from the declarations, which will default to length 1. I also omitted the string delimiters from the second declaration, causing another type of implicit conversion.

One should, by the way, not assume much more than that about ISNUMERIC, e.g.:

DECLARE @Val1 char(2) = '2D';
DECLARE @Val2 char = 3;
SELECT @Val1 + @Val2 AS Total,
CASE
WHEN ISNUMERIC(@Val1 + @Val2) = 0 THEN 'No'
WHEN ISNUMERIC(@Val1 + @Val2) = 1 THEN 'Yes'
END AS IsNumber;

This statement, under 'Working with the CAST Function':

"However, in the second instance of CAST, I do not include the length. It’s not necessary in this case because SQL Server automatically returns the DATETIME data in the predefined format, as shown in the following query results:"

is blatantly wrong. Except with constants, SQL Server cannot "automatically" choose a data type length in a CAST or CONVERT function. As stated in BOL, the default length of the target data type is 30 (unlike 1 in most statements). In this case, the expression will always be shorter than 30, so that's why specifying the length is unnecessary.

In your penultimate examples, you assume that an empty string could somehow be intended as NULL. I don't understand how anyone working with SQL could ever make such an assumption. The reason why the empty string is converted to 1900-01-01 is that it is evaluated as zero, and that is SQL Server's zero date. Try this:

SELECT CAST(2 AS datetime);

Your final example (under 'Working with the CONVERT Function') has, I surmise, some typos, although it's unclear whether you meant

SET @ProductCost = 123456789.1267;

In which case your result set should be

ProductCost Cost1 Cost2
123456789.1267 123,456,789.13 123456789.1267

or whether you meant

SET @ProductCost = 123456789.1234;

with this result set

ProductCost Cost1 Cost2
123456789.1234 123,456,789.12 123456789.1234








Subject: Use of implicit conversion in views
Posted by: Phil Factor (view profile)
Posted on: Tuesday, January 11, 2011 at 2:47 AM
Message: An interesting article full of timely reminders. Interestingly, an article today by Thom Bolin on SQL Server Central gives an additional warning of the effects on performance of having implicit conversions in views.
http://www.sqlservercentral.com/articles/Views/71787/

Subject: what about the new date&time data types?
Posted by: Henrik Staun Poulsen (view profile)
Posted on: Tuesday, January 11, 2011 at 2:48 AM
Message: Hi Ian,

your example with an integer to varchar type casting (ProductID = 350) and Index scan, does that also apply to the new date data type?

Currently we mix and match datetime and smalldatetime without any known problems, but if we start using DateTime2 or Date or DateTimeOffset, do we then run into trouble?

Best regards,
Henrik

Subject: Re: anonymous dyspeptic critic
Posted by: WBrewer (view profile)
Posted on: Tuesday, January 11, 2011 at 3:19 AM
Message: @Anonymous says "In your penultimate examples, you assume that an empty string could somehow be intended as NULL. I don't understand how anyone working with SQL could ever make such an assumption."
I think you've misunderstood what Robert was illustrating. As I read it, this is a problem that comes up when dates are imported by developers who aren't aware of the consequences. I'd agree. I often see this mistake being made, and Robert is right to warn about it. Devs will often try to represent null dates as empty strings, and Database Developers will need to be aware of it.

Subject: Regarding examples
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 11, 2011 at 9:17 AM
Message: Thanks for the article.Its very informative

The examples do not run properly in SQL SERVER 2005 (Sorry. We've now amended the statement that says they all do. Ed)

Subject: Cast for Numeric data type to Int
Posted by: Edward Boyle (not signed in)
Posted on: Wednesday, January 12, 2011 at 6:33 AM
Message: Just to clarify the Cast as Int. The Money datatype is rounded correctly but other numeric data types are truncated

Declare @listprice as Money
Set @Listprice=1431.50
select Int1=cast(@listPrice as Int),Int2=cast(1431.5 as Int)

Int1 Int2
1432 1431

Subject: Implicit Conversion
Posted by: Luan Moreno (not signed in)
Posted on: Wednesday, January 12, 2011 at 5:11 PM
Message: Robert, first congrats for the post, very good. Today I passed for this problem, your post save my day. Thanks and keep going with this excelents posts

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.