"in-line" data-type conversions? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

"in-line" data-type conversions?

Author

Topic

DesertDude
Starting Member

8 Posts

Posted - 2011-04-12 : 20:28:20

I know that this will cause an implicit conversion of the integer zero to a money type, assuming that the UnitPriceDiscount is of money type:

...WHERE UnitPriceDiscount <> 0

and that this, on the other hand, will prevent that implicit conversion because it will identify the zero as a money type from the get-go:

...WHERE UnitPriceDiscount <> $0.0

1. What are similar ways to handle other data types? This shows how to handle money, how about the other data types? A link to some Microsoft site that lists this would be great.

2. Is there a name for this? Searches for "explicit conversions" seem to hit mostly CONVERT() and CAST(), and understandably; "in-line" comes up with nothing close... so how does one refer to this type of operation?

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-13 : 10:50:08

The short answer is, if you don't cast a constant value sql will make an implicit conversion, sorta. Now, as you have shown, there are a couple of types that it knows how to deal with Money Constants (if they have the $), Nvarchar (N'') and Varchar ('') are a couple. But SQL certainly doesn't know if 1234.123 is a NUMERIC or FLOAT. So, it then goes by the order of Data Type Precedence (Transact-SQL). Here is a link:

http://msdn.microsoft.com/en-us/library/ms190309.aspx

DesertDude
Starting Member

8 Posts

Posted - 2011-04-15 : 12:42:56

Thanks for the reply. Aside from the Money ($), Nvarchar (N'') and Varchar (''), do you know of others or where I can get a list of the others? For example, how about dates? If I compare a data column to a literal value, it works, but I assume it performs an implicit conversion:

...WHERE HireDate BETWEEN '1/1/2005' and '4/8/2010'

Is there a way to tell it that those string literals are dates just like using a $ tells it that a number is money?

Thanks, again.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-15 : 12:47:34

Not without CASTing or CONVERTing.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-15 : 12:54:06

There was a system table named spt_datatype_info that had this information, but it seems to have been locked down in 2005/2008. They had columns for prefix and suffix, here are the ones that had a prefix defined:

type_name		literal_prefix	literal_suffix
binary			0x		NULL
char			'		'
datetime		'		'
image			0x		NULL
money			$		NULL
nchar			N'		'
ntext			N'		'
nvarchar		N'		'
smalldatetime		'		'
smallmoney		$		NULL
text			'		'
timestamp		0x		NULL
uniqueidentifier	'		'
varbinary		0x		NULL
varchar			'		'
xml			N'		'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources