declare @dateNow datetime declare @dateCpr1 datetime declare @dateCpr2 datetime set @dateNow = getdate() --当前日期(下文以 2021-07-28 09:04:59 为例) set @dateCpr1 = '2021-02-27 23:59:59' --给定日期1 set @dateCpr2 = '2021-07-27 10:26:00' --给定日期2
print CONVERT(varchar(100), @dateNow, 20) print CONVERT(varchar(100), @dateNow, 23) print CAST(@dateNow AS DATE) print CONVERT(varchar(100), @dateNow, 24)
输出:
2021-07-28 09:04:59 2021-07-28 2021-07-28 09:04:59
print DatePart(yy,@dateNow) print DatePart(mm,@dateNow) print DatePart(dd,@dateNow) print DatePart(hh,@dateNow) print DatePart(mi,@dateNow) print DatePart(ss,@dateNow) print DatePart(ms,@dateNow) print DatePart(qq,@dateNow) print DatePart(dy,@dateNow) print DatePart(wk,@dateNow) print DatePart(dw,@dateNow)
输出:
2021 7 28 9 4 59 3 209 31 4
if(CONVERT(varchar(100), @dateCpr1, 23) = CONVERT(varchar(100), @dateCpr2, 23)) print 1; else print 0;
输出:
0
if(CONVERT(varchar(100), @dateNow, 23) = CONVERT(varchar(100), @dateCpr1, 23)) print 1; else print 0;
输出:
0
if(@dateCpr1 > @dateCpr2) print 1 else print 0
输出:
0
if(@dateCpr2 > @dateCpr1) print 1 else print 0
输出:
0
print CONVERT(varchar(100), @dateNow+1, 23)
输出:
2021-07-29
print CONVERT(varchar(100), @dateNow-1, 23)
输出:
2021-07-27
print CONVERT(varchar(100), @dateNow, 23) + ' 0:00:00.000' print CONVERT(varchar(100), @dateNow, 23) + ' 12:00:00.000' print CONVERT(varchar(100), @dateNow, 23) + ' 23:59:59.999'
输出:
2021-07-28 0:00:00.000 2021-07-28 12:00:00.000 2021-07-28 23:59:59.999
print DATEADD(DAY, 2 - DATEPART(WEEKDAY, @dateNow), CAST(@dateNow AS DATE)) print DATEADD(DAY, 8 - DATEPART(WEEKDAY, @dateNow), CAST(@dateNow AS DATE))
输出:
2021-07-26 2021-08-01
print DATEADD(DAY, 1, EOMONTH(@dateNow, -1)) print EOMONTH (@dateNow)
输出:
2021-07-01 2021-07-31
print CAST(DATEADD(quarter, DATEDIFF(quarter, 0, @dateNow), 0) as DATE) print CAST(DATEADD (DAY, -1, DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) +1, 0)) as DATE)
输出:
2021-07-01 2021-09-30
print CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, @dateNow), 0)as DATE) print CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, -1) as DATE)
输出:
2021-01-01 2021-12-31
print DAY(EOMONTH(@dateNow))
输出:
31
print DATEPART(week, @dateNow)
输出:
31
set @weekNo = datepart(weekday , @dateNow) if(@weekNo <> 1 and @weekNo <> 7) print 1 else print 0
输出:
1
declare @weekNo int set @weekNo = datepart(weekday , @dateNow) if(@weekNo = 1 or @weekNo = 7) print 1 else print 0
输出:
0
以2021年7月为例(“首周”为红框圈出的日期):
declare @whichWeekDay int --周几(值域:[1,7],表示周一到周天) set @whichWeekDay =1 declare @firstDayOfMonth datetime set @firstDayOfMonth = DATEADD(DAY, 1, EOMONTH(@dateNow, -1)) print CAST(@firstDayOfMonth + (7 + @whichWeekDay + 1 - datepart(weekday, @firstDayOfMonth)) % 7 AS DATE)
输出:
2021-07-05