SQL中CONVERT轉換函數的用法

格式:
CONVERT(data_type,expression[,style])

說明:
此樣式一般在時間型別(datetime,smalldatetime)與字串型別(nchar,nvarchar,char,varchar)
相互轉換的時候才用相互轉換轉換的時候才用相互轉換到.

範例:
SELECT CONVERT(varchar(30),getdate(),101) now
結果為:
now
--------------------- ------------------
|09/15/2001

======================== ============================================

style數字在轉換時間時的意義如下：

------------------------------------------ -------------------------------------------------- ----------------
Style(2位元表示年份)   |  Style(4位元表示年份)   |   輸入輸出格式             
------------- -------------------------------------------------- ---------------------------------------------
0                                  | mon dd yyyy hh:miAM(或PM)              
--------------------------------------- -------------------------------------------------- -------------------
1                                mm/dd/yy                                -------------------------------------------------- ---------------------------------------
2                                yy-mm-dd
------------------------------------------------- -------------------------------------------------- ---------
3                                 dd/mm/yy                               -------------------------------------------------- -----------------------------
4                                  dd.mm.yy                              -------------------------------------------------- -------------------------------------------------
5                               |                                       ---------------------------------------------------------------- ---------------------------------
6                                      |   日 月 年                      -------------------------------------------------- -------------------------------------------------- ------------
7                                     |   星期一dd,yy                        ---------------------------------------------------------------- --------------------------------- -------------
8                                  時:分:秒                            ------------- ------------------------------------- ------------- --------------
9                                       |   星期一dd yyyy hh:mi:ss:mmmmAM(或PM)
-- ---------------------------------- ---------------- ---------------------------------- ---------------- -------
10                              月-日-年                                ----------------------------------------------------------------- ------------------------- --------------
11                                年/月/日
------------------------------------------------- ------- ------------------------------------------- ------- --------------
12                             |  ymmdd                                -------------------------------------------------- ---------------
13                             |  (24小時制)  
----------- -------------------------------------------------- -----------------------------------------------
14                             | 114                            |    
----------------------------------- -------------------------------------------------- -----------------------
20                             |  24小時制)         
---- -------------------------------------------------- -------------------------------------------------- ----
21                             |       |    yyyy-mm-dd hh:mi:ss:mmm(24小時制) 
----------------------- -------------------------------------------------- -----------------------------------

語句及查詢結果：
SELECT CONVERT(varchar(100) , GETDATE(), 0): 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 1): 05/16/06
. 。 100), GETDATE(), 5): 16-05-06
SELECT CONVERT(varchar(100), GETDATE(), 6): 16 05 06
SELECT CONVERT(7)( 05 16, 06
SELECT CONVERT(varchar(100), GETDATE(), 8): 10:57:46
SELECT CONVERT(varchar(100), GETDATE(SELECT CONVER 827AM
SELECT CONVERT(varchar(100), GETDATE(), 10): 05-16-06
SELECT CONVERT(varchar(100), GETDATE(), 11):506/0char(100), GETDATE(), 11):06/0 ), GETDATE(), 12): 060516
SELECT CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937,CONVER ): 10:57:46:967
SELECT CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
SELECT CONVERTvar1(10TD : 2006-05-16 10:57:47.157
SELECT CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 22): 05/16/06 10:57:47 AM], , 23): 2006-05-16
SELECT CONVERT(varchar(100), GETDATE(), 24): 10:57:47
SELECT CONVERT(varchar(100), 25: 16 10:57:47.250
SELECT CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
): 05 16 2006 10:57AM
): 6
SELECT CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
SELECT CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
SECT COivERT( ), 104): 16.05.2006
選擇轉換(varchar(100), GETDATE(), 105): 16-05-2006
選擇轉換(varchar(100), GETDATE(), 106):16 CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
SELECT CONVERT(varchar(100), GETDATE(), 108): 10:57:49
SELECT CONVERT(var:57:49
SELECT CONVERT(var)(TD. ), 109): 05 16 2006 10:57:49:437AM
選擇轉換(varchar(100), GETDATE(), 110): 05-16-2006
選擇轉換(varchar(100), GETDATE( ): 2006/05/16
選擇轉換(varchar(100), GETDATE(), 112): 20060516
選擇轉換(varchar(100), GETDATE(), 113): 16 0510: 16
選擇CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
SELECT CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:57選擇CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49:000