Óï·¨½á¹¹£º

1. Cast

Óï·¨½á¹¹£ºCAST ( expression AS data_type [ ( length ) ] )

2. Convert

Óï·¨½á¹¹£ºCONVERT ( data_type [ ( length ) ] , expression [ , style ] )

3. Parse

Óï·¨½á¹¹£ºPARSE ( string_value AS data_type [ USING culture ] )

ÆäÖУ¬Parse Ϊ Sql Server µÄÐÂÌØÐÔ expression ±ØÐëҪΪ±»×ª»»ÀàÐ͵Ä×Ö·û´®ÐÎʽ

ÐÔÄÜ·ÖÎö£º

×ܽ᣺

1. Convert ºÍ Cast µÄÇø±ð

CONVERTÊÇר¶ÔSQL ServerʹÓõģ¬Ê¹ÈÕÆÚÓëʱ¼äÖµ£¬Ð¡ÊýÖ®¼äת»»¾ßÓиü¿íµÄÁé»îÐÔ¡£

CASTÊÇÁ½ÖÖ¹¦ÄÜÖиü¾ßANSI±ê×¼µÄ¹¦ÄÜ£¬¼´ËäÈ»¸ü¾ß±ãЯÐÔ£¨±ÈÈ磬ʹÓÃCASTµÄº¯ÊýÄܸüÈÝÒ׵ı»ÆäËüÊý¾Ý¿âÈí¼þʹÓã©£¬µ«¹¦ÄÜÏà¶ÔÈõһЩ¡£²»¹ý£¬µ±Ð¡Êýת»¯ÎªÊýÖµ£¬²¢±£Áôԭʼ±í´ïʽÖеÄСÊýÊýֵʱ£¬ÈÔÈ»ÐèҪʹÓÃCAST

2. Convert ºÍ Parse µÄÇø±ð

Á½Õß³ýÁËÓï·¨µÄÇø±ð£¬¾ÍÊǹ¦ÄÜÉÏÃæµÄÇø±ð£¬Convert ¹¦ÄܸüÇ¿´ó£¬Parse ÔòΪ Sql Server 2012 ÐÂÔöµÄ¹¦ÄÜ£¬Í¬Ê±¹¦ÄÜÈõСºÜ¶àµÄ£¬Ö»Äܽ« ×Ö·û´® ת»»Îª Êý×ÖÀàÐÍ »òÕß ÈÕÆÚÀàÐÍ£¬ÒòΪÊý×ÖÀàÐÍ »ò ÈÕÆÚÀàÐͳ¤¶È¶¼ºÜСµÄ£¬ËùÒÔ Parse Ëù´øÀ´µÄÐÔÄܼ¸ºõ²»¿ÉÄÜÓдóµÄ¸Ä±äµÄ£¬Ö®ºóÎÒÒ²×öÁË´óÁ¿µÄ±È½Ï£¬·¢ÏÖ Parse ÊÇÔÚ¾ø´ó¶àÊýÇé¿ö϶¼ÊÇ¿ÉÒÔÌæ´úµÄ

3. ×ۺϱȽÏ

a. Èç¹û ÊÇ×öÕë¶Ô Sql Server µÄ×ö¿ª·¢£¬½¨ÒéÖ»Óà Convert ·½·¨

b. Èç¹û¿¼ÂÇÓï¾ä¿çƽ̨£¬Ôò¾¡Á¿Óà Cast£¬Èç SQL Óï¾ä¼ÈÄÜÔÚ Sql Server ÉÏÖ´ÐУ¬ÓÖÄÜÔÚ MySql ÉÏÃæÖ´ÐÐ

c. Parse ÔÚ Sql Server 2012 ÖоÍÊÇÒ»¸ö¼¦Àߣ¬¾­¹ý1 ÏÂÎçµÄʵսÑо¿ºÍ·­ÔÄMSDN£¬¶¼Ã»ÓÐÕÒµ½ËüµÄÓÅÊÆ£¬Èç¹û±ÈÓï·¨£¬Cast µÄÓï·¨½á¹¹ºÍËüÒ»Ñù£¬Èç¹û±È¹¦ÄÜ£¬ÓÖÌ«ÈõС£¬ÍêÈ«²»ÄܺÍCast ºÍ Convert ×ö±È½Ï£¬ËùÒÔ£¬Äú¾Í°Ñ Parse Íü¼Çµô°É

ÐÔÄÜ·ÖÎöËù×öµÄʵÑ飺

1. Convert ºÍ Parse ת»»Îª×Ö·û´®µÄ±È½Ï

¸´ÖÆ´úÂë ´úÂëÈçÏÂ:

DBCC FreeProcCache

set statistics profile on

select top 1 parse(name as bigint) from test_Parse

select top 1 convert(bigint,name) from test_Parse

ÆäÖÐ name Ϊ nvarchar(1000) µÄÒ»¸öÒ»¶Î£¬ÖµÎª£º1231238912378912128

Ö´ÐйýÖ®ºó£¬»á·¢ÏÖ£¬Á½ÕßµÄÖ´Ðмƻ®ÍêÈ«Ïàͬ£¬ÎÞÂÛÊÇ¶Ô CPU µÄÏûºÄ£¬»¹ÊÇ¶Ô IOµÄÏûºÄ£¬Ã»ÓÐÈκÎÐÔÄܲî±ð

2. Money ºÍ DateTime ÀàÐ͵Äת»»±È½Ï

¸´ÖÆ´úÂë ´úÂëÈçÏÂ:

-- DateTime

DBCC FreeProcCache

declare @date varchar(100) = '2012/07/19 14:57:09.760'

select CONVERT(datetime,@date) as date

select Parse(@date as datetime using 'zh-CN') as date

--Money

SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result

Select Convert(money,'€345,98',1) AS Result

3. Parse µÄרÀû£¬×Ö·ûÐÍÈÕÆÚµ½ datetime µÄת»»

¸´ÖÆ´úÂë ´úÂëÈçÏÂ:

SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result

SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'zh-CN') AS Result

-- Cast ºÍ Convert ¶ÔÕâÖÖÀàÐ͵Ä×Ö·û´®¶¼ÊDz»Ö§³ÖµÄ

--SELECT cast('Monday, 13 December 2010' AS datetime2) AS Result

--SELECT Convert(datetime,'Monday, 13 December 2010') AS Result

ÔÚÐÔÄÜ·½ÃæͬÑùûʲôÇø±ð£¬µ«ÊDZȽϹ¦ÄÜʱ£¬Parse È´Òª±È Convert ´íÌ«¶à

ÑÏÖØ»³ÒÉ£¬ Parse ±¾È˲¢Ã»ÓÐÕÒµ½Ì«¶àµÄÓÅÊƺÍÓ¦Óó¡¾°£¬»òÕßÓÉÓÚ±¾È˲ÅÊèѧdz£¬ÕÒ²»µ½¸üºÃµÄ£¬Óж®µÃ£¬Çë´Í½Ì