一周的第一天
select
@@DATEFIRST
一个月的第一天
select
dateadd
(mm,
datediff
(mm,
0
,
getdate
()),
0
)
本周的星期一
select
dateadd
(wk,
datediff
(wk,
0
,
getdate
()),
0
)
一年的第一天
SELECT
DATEADD
(yy,
DATEDIFF
(yy,
0
,
getdate
()),
0
)
季度的第一天
SELECT
DATEADD
(qq,
DATEDIFF
(qq,
0
,
getdate
()),
0
)
当天的零时
SELECT
DATEADD
(dd,
DATEDIFF
(dd,
0
,
getdate
()),
0
)
上个月的最后一天 :本月第一天减2ms.
SELECT
dateadd
(ms,
-
2
,
DATEADD
(mm,
DATEDIFF
(mm,
0
,
getdate
()),
0
))
本月的最后一天
SELECT
dateadd
(ms,
-
2
,
DATEADD
(mm,
DATEDIFF
(m,
0
,
getdate
())
+
1
,
0
))
本月的第一个星期一 去掉时分秒
DATEADD
(
day
,
DATEDIFF
(
day
,
0
,
getdate
()),
0
)
显示星期几
select
datename
(weekday,
getdate
())
如何取得某个月的天数
SELECT
Day
(
dateadd
(ms,
-
3
,
DATEADD
(mm,
DATEDIFF
(m,
0
,
getdate
())
+
1
,
0
)))
判断是否闰年:
SELECT
case
day
(
dateadd
(mm,
2
,
dateadd
(ms,
-
3
,
DATEADD
(yy,
DATEDIFF
(yy,
0
,
getdate
()),
0
))))
when
28
then
'
平年
'
else
'
闰年
'
end
一个季度多少天
declare
@m
tinyint
,
@time
smalldatetime
select
@m
=
month
(
getdate
())
select
@m
=
case
when
@m
between
1
and
3
then
1
when
@m
between
4
and
6
then
4
when
@m
between
7
and
9
then
7
else
10
end
select
@time
=
datename
(
year
,
getdate
())
+
'
-
'
+
convert
(
varchar
(
10
),
@m
)
+
'
-01
'
select
datediff
(
day
,
@time
,
dateadd
(mm,
3
,
@time
))