SQL ʹÓà CONVERT
ʹÓà CONVERT£º
CONVERT (data_type[(length)], expression [, style])
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
½«Ä³ÖÖÊý¾ÝÀàÐ͵ıí´ïʽÏÔʽת»»ÎªÁíÒ»ÖÖÊý¾ÝÀàÐÍ¡£CAST ºÍ CONVERT ÌṩÏàËÆµÄ¹¦ÄÜ¡£
Óï·¨
ʹÓà CAST£º
CAST ( expression AS data_type )
ʹÓà CONVERT£º
CONVERT (data_type[(length)], expression [, style])
²ÎÊý
expression
ÊÇÈκÎÓÐЧµÄ Microsoft® SQL Server™ ±í´ïʽ¡£Óйظü¶àÐÅÏ¢£¬Çë²Î¼û±í´ïʽ¡£
data_type
Ä¿±êϵͳËùÌṩµÄÊý¾ÝÀàÐÍ£¬°üÀ¨ bigint ºÍ sql_variant¡£²»ÄÜʹÓÃÓû§¶¨ÒåµÄÊý¾ÝÀàÐÍ¡£ÓйؿÉÓõÄÊý¾ÝÀàÐ͵ĸü¶àÐÅÏ¢£¬Çë²Î¼ûÊý¾ÝÀàÐÍ¡£
length
nchar¡¢nvarchar¡¢char¡¢varchar¡¢binary »ò varbinary Êý¾ÝÀàÐ͵ĿÉÑ¡²ÎÊý¡£
style
ÈÕÆÚ¸ñʽÑùʽ£¬½èÒÔ½« datetime »ò smalldatetime Êý¾Ýת»»Îª×Ö·ûÊý¾Ý£¨nchar¡¢nvarchar¡¢char¡¢varchar¡¢nchar »ò nvarchar Êý¾ÝÀàÐÍ£©£»»òÕß×Ö·û´®¸ñʽÑùʽ£¬½èÒÔ½« float¡¢real¡¢money »ò smallmoney Êý¾Ýת»»Îª×Ö·ûÊý¾Ý£¨nchar¡¢nvarchar¡¢char¡¢varchar¡¢nchar »ò nvarchar Êý¾ÝÀàÐÍ£©¡£
SQL Server Ö§³ÖʹÓÿÆÍþÌØËã·¨µÄ°¢À²®ÑùʽÖеÄÊý¾Ý¸ñʽ¡£
ÔÚ±íÖУ¬×ó²àµÄÁ½Áбíʾ½«
Ïà¹ØÎĵµ£º
ÔÚ2005ÖÐÓÐͬÒå´ÊÓë¸´ÖÆµÄ¸ÅÄî
ͬÒå´ÊµÄÖ÷Òª×÷ÓÃÊÇ£º
Ò»£ºË޶̶ÔÏóµÄÃû³Æ£¬¼õÉÙ¹¤×÷ÈËÔ±ÊéдµÄʱ¼ä£¬Ìá¸ßЧÂÊ¡£ÎÒÃÇÖªµÀ·ÃÎÊÊý¾Ý¿âÒ»¸ö¶ÔÏóµÄͨ³£×îÈ«µÄ¶ÔÏóÃû³ÆÊÇ£º·þÎñÆ÷Ãû³Æ¡£Êý¾Ý¿âÃû³Æ¡£¼Ü¹¹Ãû³Æ¡£¶ÔÏóÃû³Æ
¶þ£ºÍ¬²½Êý¾Ý¡£ ......
ÄãÊÇ·ñÔÚǧ·½°Ù¼ÆÓÅ»¯SQL Server Êý¾Ý¿âµÄÐÔÄÜ?Èç¹ûÄãµÄÊý¾Ý¿âÖк¬ÓдóÁ¿µÄ±í¸ñ£¬°ÑÕâЩ±í¸ñ·ÖÇø·ÅÈë¶ÀÁ¢µÄÎļþ×é¿ÉÄÜ»áÈÃÄãÊÜÒæ·Ëdz¡£SQL Server 2005ÒýÈëµÄ±í·ÖÇø¼¼Êõ£¬ÈÃÓû§Äܹ»°ÑÊý¾Ý·ÖÉ¢´æ·Åµ½²»Í¬µÄÎïÀí´ÅÅÌÖУ¬Ìá¸ßÕâЩ´ÅÅ̵IJ¢Ðд¦ÀíÐÔÄÜÒÔÓÅ»¯²éѯÐÔÄÜ¡£
¡¡¡¡SQL ServerÊý¾Ý¿â±í·ÖÇø²Ù×÷¹ý³ÌÓÉÈý¸ö²½Öè×é³É£º
¡ ......
ÀýÈçÎÊÌ⣺ÏÖÔÚÄãÃæ¶ÔÒ»Õűí table1 , table1ÖÐÓиö×Ö¶ÎΪsales_salary £¬ÔÚÊý¾Ý¿â´æ·ÅµÄ×Ö¶ÎΪint ÀàÐÍ ¡£
ÒªÇó£¬Äãͳ¼ÆµÄ½á¹ûµ¥Î»£¨ÍòÔª£©£¬±£Áô2λСÊý¡£²¢ÇÒ»áÓÐÕâÑùµÄµÈʽ £¨1ÐУ«2ÐУ½3ÐУ½7ÐУ«8ÐУ© Ãæ¶ÔÕâÑùµÄÎÊÌ⣬½â¾öµÄ·½°¸Óкܶࡣ±ÈÈ磬Äã¿ÉÒÔͨ¹ýÊÓͼµÄ·½°¸À´½â¾ö£¬»ò¿ØÖÆÊäÈëÓò ...
µ«ÓÐÒ»ÖÖµÈЧ¿ØÖÆÊäÈë ......
--1.¹ØÓÚwhereɸѡÆ÷ÖгöÏÖÖ¸¶¨ÐÇÆÚ¼¸µÄÇó½â
SQL code
--»·¾³
create table test_1
(
id int,
value varchar(10),
t_time datetime
)
insert test_1
select 1,'a','2009-04-19' union
select 2,'b','2009-04-20' union
select 3,'c','2009-04-21' union
select 4,'d','2009-04-22' union
s ......
merge [target] t
using [source] s on t.id = s.id
when matched then update t.name = s.name, t.age = s.age -- use "rowset1"
when not matched then insert values(id,name,age) -- use "rowset2"
when source not matched then delete; -- use "rowset3"
MERGE dbo.table AS im ......