SQL SERVER时间格式

最新推荐文章于 2024-09-18 17:45:41 发布

weixin_34082177

最新推荐文章于 2024-09-18 17:45:41 发布

阅读量91

点赞数

原文链接：http://www.cnblogs.com/tulong-wanglinglong/archive/2010/05/07/1729760.htm

版权

最近在写程序的过程中有遇到SQL SERVER时间格式的问题，一时想不起来，上网搜索了一下，这方面的资料还真不少，找了一个比较全面的。

具体的网址倒是没保存，可是我这个人习惯把好的代码及其方法保存下来，现在贴出来，希望原作者体谅。

代码

1 -- -上个月月初第一天
2 select CONVERT ( varchar ( 12 ) , DATEADD (mm, DATEDIFF (mm, 0 , dateadd (mm, - 1 , getdate ())), 0 ), 112 )
3
4 -- -上个月月末最后一天
5 select CONVERT ( varchar ( 12 ), dateadd (ms, - 3 , DATEADD (mm, DATEDIFF (m, 0 , getdate ()), 0 )), 112 )
6
7 -- 1. 显示本月第一天
8
9 SELECT DATEADD (mm, DATEDIFF (mm, 0 , getdate ()), 0 )
10
11 select convert ( datetime , convert ( varchar ( 8 ), getdate (), 120 ) + ' 01 ' , 120 )
12
13 -- 2. 显示本月最后一天
14
15 select dateadd ( day , - 1 , convert ( datetime , convert ( varchar ( 8 ), dateadd ( month , 1 , getdate ()), 120 ) + ' 01 ' , 120 ))
16
17 SELECT dateadd (ms, - 3 , DATEADD (mm, DATEDIFF (m, 0 , getdate ()) + 1 , 0 ))
18
19 -- 3. 上个月的最后一天
20
21 SELECT dateadd (ms, - 3 , DATEADD (mm, DATEDIFF (mm, 0 , getdate ()), 0 ))
22
23 -- 4. 本月的第一个星期一
24 select DATEADD (wk, DATEDIFF (wk, 0 , dateadd (dd, 6 - datepart ( day , getdate ()), getdate ())), 0 )
25
26 -- 5. 本年的第一天
27
28 SELECT DATEADD (yy, DATEDIFF (yy, 0 , getdate ()), 0 )
29
30 -- 6. 本年的最后一天
31
32 SELECT dateadd (ms, - 3 , DATEADD (yy, DATEDIFF (yy, 0 , getdate ()) + 1 , 0 ))
33
34 -- 7. 去年的最后一天
35
36 SELECT dateadd (ms, - 3 , DATEADD (yy, DATEDIFF (yy, 0 , getdate ()), 0 ))
37
38 -- 8. 本季度的第一天
39
40 SELECT DATEADD (qq, DATEDIFF (qq, 0 , getdate ()), 0 )
41
42 -- 9. 本周的星期一
43
44 SELECT DATEADD (wk, DATEDIFF (wk, 0 , getdate ()), 0 )
45
46 -- 10. 查询本月的记录
47
48 select * from tableName where DATEPART (mm, theDate) = DATEPART (mm, GETDATE ()) and DATEPART (yy, theDate) = DATEPART (yy, GETDATE ())
49
50 -- 11. 查询本周的记录
51
52 select * from tableName where DATEPART (wk, theDate) = DATEPART (wk, GETDATE ()) and DATEPART (yy, theDate) = DATEPART (yy, GETDATE ())
53
54 -- 12 查询本季的记录注：其中:GETDATE() 是获得系统时间的函数。
55
56 select * from tableName where DATEPART (qq, theDate) = DATEPART (qq, GETDATE ()) and DATEPART (yy, theDate) = DATEPART (yy, GETDATE ())
57
58 -- 13. 获取当月总天数:
59
60 select DATEDIFF (dd, getdate (), DATEADD (mm, 1 , getdate ()))
61
62 select datediff ( day , dateadd (mm, datediff (mm, '' , getdate ()), '' ), dateadd (mm, datediff (mm, '' , getdate ()), ' 1900-02-01 ' ))
63
64 -- 14. 获取当前为星期几
65
66 SELECT DATENAME (weekday, getdate ())
67
68
69
70 -- -----------------------------------------------------------------------------------------
71
72 select CONVERT ( varchar ( 12 ) , getdate (), 101 ) -- 12/28/2007
73
74 select CONVERT ( varchar ( 12 ) , getdate (), 102 ) -- 2007.12.28
75
76 select CONVERT ( varchar ( 12 ) , getdate (), 103 ) -- 28/12/2007
77
78 select CONVERT ( varchar ( 12 ) , getdate (), 104 ) -- 28.12.2007
79
80 select CONVERT ( varchar ( 12 ) , getdate (), 105 ) -- 28-12-2007
81
82 select CONVERT ( varchar ( 12 ) , getdate (), 106 ) -- 28 12 2007
83
84 select CONVERT ( varchar ( 12 ) , getdate (), 107 ) -- 12 28, 2007
85
86 select CONVERT ( varchar ( 12 ) , getdate (), 108 ) -- 10:06:33
87
88 select CONVERT ( varchar ( 12 ) , getdate (), 109 ) -- 12 28 2007 1
89
90 select CONVERT ( varchar ( 12 ) , getdate (), 110 ) -- 12-28-2007
91
92 select CONVERT ( varchar ( 12 ) , getdate (), 111 ) -- 2007/12/28
93
94 select CONVERT ( varchar ( 12 ) , getdate (), 112 ) -- 20071228
95
96 select CONVERT ( varchar ( 12 ) , getdate (), 113 ) -- 28 12 2007 1
97
98 select CONVERT ( varchar ( 12 ) , getdate (), 114 ) -- 10:07:36:623
99
100 select CONVERT ( varchar , getdate (), 120 ) -- 2007-12-28 10:08:56
101
102 select CONVERT ( varchar ( 10 ), getdate (), 120 ) -- 2007-12-28
103
104 select replace ( replace ( replace ( CONVERT ( varchar , getdate (), 120 ), ' - ' , '' ), ' ' , '' ), ' : ' , '' ) -- 20071228101012

转载于:https://www.cnblogs.com/tulong-wanglinglong/archive/2010/05/07/1729760.html