(1)日期转换
1. Select CONVERT(varchar(100), GETDATE(), 0) //01 17 2011 8:46AM
2. Select CONVERT(varchar(100), GETDATE(), 1) //01 17 2011 8:46AM
3. Select CONVERT(varchar(100), GETDATE(), 2) //11.01.17
4. Select CONVERT(varchar(100), GETDATE(), 3) //17/01/11
5. Select CONVERT(varchar(100), GETDATE(), 4) //17.01.11
6. Select CONVERT(varchar(100), GETDATE(), 5) //17-01-11
7. Select CONVERT(varchar(100), GETDATE(), 6) //17 01 11
8. Select CONVERT(varchar(100), GETDATE(), 7) //01 17, 11
9. Select CONVERT(varchar(100), GETDATE(), 8) //08:50:44
10. Select CONVERT(varchar(100), GETDATE(), 9) //01 17 2011 8:51:08:793AM
11. Select CONVERT(varchar(100), GETDATE(),10) //01-17-11
12. Select CONVERT(varchar(100), GETDATE(),11) //11/01/17
13. Select CONVERT(varchar(100), GETDATE(),12) //110117
14. Select CONVERT(varchar(100), GETDATE(),13) //17 01 2011 08:53:18:340
15. Select CONVERT(varchar(100), GETDATE(),14) //08:54:16:607
16. Select CONVERT(varchar(100), GETDATE(),20) //2011-01-17 08:55:07
17. Select CONVERT(varchar(100), GETDATE(),21) //2011-01-17 08:56:03.623
18. Select CONVERT(varchar(100), GETDATE(),22) //01/17/11 8:56:31 AM
19. Select CONVERT(varchar(100), GETDATE(),23) //2011-01-17
20. Select CONVERT(varchar(100), GETDATE(),24) //09:03:36
21. Select CONVERT(varchar(100), GETDATE(),25) //2011-01-17 09:03:55.950
22. Select CONVERT(varchar(100), GETDATE(),100) //01 17 2011 9:04AM
23. Select CONVERT(varchar(100), GETDATE(),101) //01/17/2011
24. Select CONVERT(varchar(100), GETDATE(),102) //2011.01.17
25. Select CONVERT(varchar(100), GETDATE(),103) //17/01/2011
26. Select CONVERT(varchar(100), GETDATE(),104) //17.01.2011
27. Select CONVERT(varchar(100), GETDATE(),105) //17-01-2011
28. Select CONVERT(varchar(100), GETDATE(),106) //17 01 2011
29. Select CONVERT(varchar(100), GETDATE(),107) //01 17, 2011
30. Select CONVERT(varchar(100), GETDATE(),108) //09:17:03
31. Select CONVERT(varchar(100), GETDATE(),109) //01 17 2011 9:17:23:280AM
32. Select CONVERT(varchar(100), GETDATE(),110) //01-17-2011
33. Select CONVERT(varchar(100), GETDATE(),111) //2011/01/17
34. Select CONVERT(varchar(100), GETDATE(),112) //20110117
35. Select CONVERT(varchar(100), GETDATE(),113) //17 01 2011 09:18:33:560
36. Select CONVERT(varchar(100), GETDATE(), 114) //09:19:03:560
37. Select CONVERT(varchar(100), GETDATE(),120) //2011-01-17 09:20:16
38. Select CONVERT(varchar(100), GETDATE(),121) //2011-01-17 09:20:35.000
39. Select CONVERT(varchar(100), GETDATE(),126) //2011-01-17T09:21:32.750
40. Select CONVERT(varchar(100), GETDATE(),130) //12 ??? 1432 9:22:10:043AM
41. Select CONVERT(varchar(100), GETDATE(),131) //12/02/1432 9:22:32:450AM
(2)时间差 DATEDIFF(datepart,startdate,enddate)
datepart | 缩写 |
---|---|
年 | yy, yyyy |
季度 | qq, q |
月 | mm, m |
年中的日 | dy, y |
日 | dd, d |
周 | wk, ww |
星期 | dw, w |
小时 | hh |
分钟 | mi, n |
秒 | ss, s |
毫秒 | ms |
微妙 | mcs |
纳秒 | ns |
(3)时间相加 DateAdd( date-part, span-date, date-expression ):
例:select DateAdd(month ,10,getdate())
date-part:year | quarter | month | week | day | hour | minute | second | millisecond