SQL日期转换函数

DECLARE @t TABLE(id INT IDENTITY,Code VARCHAR(35),Result NVARCHAR(30))

INSERT @t

SELECT 'CONVERT(VARCHAR,GETDATE(),0)',CONVERT(VARCHAR,GETDATE(),0) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),1)',CONVERT(VARCHAR,GETDATE(),1) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),2)',CONVERT(VARCHAR,GETDATE(),2) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),3)',CONVERT(VARCHAR,GETDATE(),3) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),4)',CONVERT(VARCHAR,GETDATE(),4) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),5)',CONVERT(VARCHAR,GETDATE(),5) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),6)',CONVERT(VARCHAR,GETDATE(),6) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),7)',CONVERT(VARCHAR,GETDATE(),7) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),8)',CONVERT(VARCHAR,GETDATE(),8) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),9)',CONVERT(VARCHAR,GETDATE(),9) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),10)',CONVERT(VARCHAR,GETDATE(),10) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),11)',CONVERT(VARCHAR,GETDATE(),11) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),12)',CONVERT(VARCHAR,GETDATE(),12) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),13)',CONVERT(VARCHAR,GETDATE(),13) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),14)',CONVERT(VARCHAR,GETDATE(),14) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),20)',CONVERT(VARCHAR,GETDATE(),20) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),21)',CONVERT(VARCHAR,GETDATE(),21) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),22)',CONVERT(VARCHAR,GETDATE(),22) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),23)',CONVERT(VARCHAR,GETDATE(),23) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),24)',CONVERT(VARCHAR,GETDATE(),24) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),25)',CONVERT(VARCHAR,GETDATE(),25) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),100)',CONVERT(VARCHAR,GETDATE(),100) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),101)',CONVERT(VARCHAR,GETDATE(),101) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),102)',CONVERT(VARCHAR,GETDATE(),102) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),103)',CONVERT(VARCHAR,GETDATE(),103) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),104)',CONVERT(VARCHAR,GETDATE(),104) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),105)',CONVERT(VARCHAR,GETDATE(),105) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),106)',CONVERT(VARCHAR,GETDATE(),106) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),107)',CONVERT(VARCHAR,GETDATE(),107) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),108)',CONVERT(VARCHAR,GETDATE(),108) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),109)',CONVERT(VARCHAR,GETDATE(),109) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),110)',CONVERT(VARCHAR,GETDATE(),110) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),111)',CONVERT(VARCHAR,GETDATE(),111) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),112)',CONVERT(VARCHAR,GETDATE(),112) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),113)',CONVERT(VARCHAR,GETDATE(),113) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),114)',CONVERT(VARCHAR,GETDATE(),114) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),120)',CONVERT(VARCHAR,GETDATE(),120) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),121)',CONVERT(VARCHAR,GETDATE(),121) UNION ALL

SELECT 'CONVERT(VARCHAR,GETDATE(),126)',CONVERT(VARCHAR,GETDATE(),126) UNION ALL

SELECT 'CONVERT(NVARCHAR,GETDATE(),130)',CONVERT(NVARCHAR,GETDATE(),130)

 

SELECT * FROM @t

/*

id          Code                                Result

----------- ----------------------------------- ------------------------------

1           CONVERT(VARCHAR,GETDATE(),0)        07  6 2009  9:31PM

2           CONVERT(VARCHAR,GETDATE(),1)        07/06/09

3           CONVERT(VARCHAR,GETDATE(),2)        09.07.06

4           CONVERT(VARCHAR,GETDATE(),3)        06/07/09

5           CONVERT(VARCHAR,GETDATE(),4)        06.07.09

6           CONVERT(VARCHAR,GETDATE(),5)        06-07-09

7           CONVERT(VARCHAR,GETDATE(),6)        06 07 09

8           CONVERT(VARCHAR,GETDATE(),7)        07 06, 09

9           CONVERT(VARCHAR,GETDATE(),8)        21:31:47

10          CONVERT(VARCHAR,GETDATE(),9)        07  6 2009  9:31:47:607PM

11          CONVERT(VARCHAR,GETDATE(),10)       07-06-09

12          CONVERT(VARCHAR,GETDATE(),11)       09/07/06

13          CONVERT(VARCHAR,GETDATE(),12)       090706

14          CONVERT(VARCHAR,GETDATE(),13)       06 07 2009 21:31:47:607

15          CONVERT(VARCHAR,GETDATE(),14)       21:31:47:607

16          CONVERT(VARCHAR,GETDATE(),20)       2009-07-06 21:31:47

17          CONVERT(VARCHAR,GETDATE(),21)       2009-07-06 21:31:47.607

18          CONVERT(VARCHAR,GETDATE(),22)       07/06/09  9:31:47 PM

19          CONVERT(VARCHAR,GETDATE(),23)       2009-07-06

20          CONVERT(VARCHAR,GETDATE(),24)       21:31:47

21          CONVERT(VARCHAR,GETDATE(),25)       2009-07-06 21:31:47.607

22          CONVERT(VARCHAR,GETDATE(),100)      07  6 2009  9:31PM

23          CONVERT(VARCHAR,GETDATE(),101)      07/06/2009

24          CONVERT(VARCHAR,GETDATE(),102)      2009.07.06

25          CONVERT(VARCHAR,GETDATE(),103)      06/07/2009

26          CONVERT(VARCHAR,GETDATE(),104)      06.07.2009

27          CONVERT(VARCHAR,GETDATE(),105)      06-07-2009

28          CONVERT(VARCHAR,GETDATE(),106)      06 07 2009

29          CONVERT(VARCHAR,GETDATE(),107)      07 06, 2009

30          CONVERT(VARCHAR,GETDATE(),108)      21:31:47

31          CONVERT(VARCHAR,GETDATE(),109)      07  6 2009  9:31:47:607PM

32          CONVERT(VARCHAR,GETDATE(),110)      07-06-2009

33          CONVERT(VARCHAR,GETDATE(),111)      2009/07/06

34          CONVERT(VARCHAR,GETDATE(),112)      20090706

35          CONVERT(VARCHAR,GETDATE(),113)      06 07 2009 21:31:47:607

36          CONVERT(VARCHAR,GETDATE(),114)      21:31:47:607

37          CONVERT(VARCHAR,GETDATE(),120)      2009-07-06 21:31:47

38          CONVERT(VARCHAR,GETDATE(),121)      2009-07-06 21:31:47.607

39          CONVERT(VARCHAR,GETDATE(),126)      2009-07-06T21:31:47.607

40          CONVERT(NVARCHAR,GETDATE(),130)     14 رجب 1430  9:31:47:607PM

 

(40 行受影响)

*/