mssql小课堂第2课 SQL Server之日期和时间函数

返回当前系统日期和时间
Getdate()

select Getdate() //结果为：2011-06-17 09:01:00.293
返回表示指定日期的“日”、“月”、“年” 部分的正数
Day()、Month（）、Year（）
select day('1989-3-2')
select day('1989/3/2')
select day('3/2/1989') //结果为：2
返回给指定日期加上一个时间间隔后的新 datetime 值
Dateadd（datepart , number, date）
datepart：指定要返回新值的日期的组成部分。{year,quarter,month,dayofyear,day,week,weekday,hour,minute,second,millisecond}
number：用于与 datepart 相加的值。
select Dateadd(Month,1,'2011-6-17') //返回2011年6月17日下1个月的同一天
select Dateadd(Month,1,'2011/6/17') //结果为2011-07-17 00:00:00.000
返回跨两个指定日期的日期边界数和时间边界数
Datediff(datepart , startdate , enddate)
datepart：指定应在日期的哪一部分计算差额的参数。{year,quarter,month,dayofyear,day,week,hour,minute,second,millisecond}
select Datediff(Month,'2011-6-17','2011-8-9') //结果为：2
返回表示指定日期的指定日期部分的字符串
Datename(datepart ,date )
datepart：是指定要返回的日期部分的参数。{year,quarter,month,dayofyear,day,week,weekday,hour,minute,second,millisecond}
weekday (dw，w) 日期部分返回星期几（如星期日、星期一等）。
select Datename(weekday,'2011-6-17') //结果为：星期五
返回表示指定日期的指定日期部分的整数
Datepart(datepart , date)
datepart：指定要返回的日期部分的参数。{year,quarter,month,dayofyear,day,week,weekday,hour,minute,second,millisecond}
select Datepart(Weekday,'2011-6-17') //结果为：6
weekday (dw) 日期部分返回与一周的某一天对应的数字，例如：Sunday = 1, Saturday = 7。
weekday 日期部分生成的数字取决于 SET DATEFIRST 所设置的值。这设置一周中的第一天。
返回表示当前 UTC 时间的 datetime 值
Getutcdate()
当前的 UTC 时间得自当前的本地时间和运行 Microsoft SQL Server 实例的计算机操作系统中的时区设置。
select Getdate() //结果为：2011-06-17 11:14:14.357
select Getutcdate() //结果为：2011-06-17 03:14:14.357

日期的推算（转）

DECLARE @dt datetime
SET @dt=GETDATE()

DECLARE @number int
SET @number=3

--1．指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+ '1-1 '

--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+ '12-31 '

--2．指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt)-2,
@dt),
120)+ '1 ')

--B. 季度的最后一天（CASE判断法）
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)
+CASE WHEN DATEPART(Quarter,@dt) in(1,4)
THEN '31 'ELSE '30 ' END)

--C. 季度的最后一天（直接推算法）
SELECT DATEADD(Day,-1,
CONVERT(char(8),
DATEADD(Month,
1+DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)+ '1 ')

--3．指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+ '1 ')

--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+ '1 ')

--C. 月的最后一天（容易使用的错误方法）
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))

--4．指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)

--5．指定日期所在周的任意星期几
--A. 星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

--B. 星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)

SELECT @Date AS '目前时间'
,DATEADD(DD,-1,@Date) AS '前一天'
,DATEADD(DD,1,@Date) AS '后一天'
/*月计算*/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通过月运算，保证日恒久为1号
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'--找到下月初再扣减1天，建议使用DATEADD而不要直接“-1”
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天'
/*周计算*/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此处与@@datefirst的值有关
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此处与@@datefirst的值有关,其他天数类推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'--注意此处与@@datefirst的值有关
,DATENAME(WEEKDAY,@Date) AS '本日是周几'
,DATEPART(WEEKDAY,@Date) AS '本日是周几'--返回值 1-星期日，2-星期一，3-星期二......7-星期六
/*年度计算*/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/*季度计算*/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'
SELECT @Date AS '目前时间' ,DATEADD(DD,-1,@Date) AS '前一天' ,DATEADD(DD,1,@Date) AS '后一天' /*月计算*/ ,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通过月运算，保证日恒久为1号 ,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'--找到下月初再扣减1天，建议使用DATEADD而不要直接“-1” ,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)' ,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天' ,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天' ,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天' ,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天' /*周计算*/ ,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此处与@@datefirst的值有关 ,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此处与@@datefirst的值有关 ,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此处与@@datefirst的值有关,其他天数类推 ,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'--注意此处与@@datefirst的值有关 ,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'--注意此处与@@datefirst的值有关 ,DATENAME(WEEKDAY,@Date) AS '本日是周几' ,DATEPART(WEEKDAY,@Date) AS '本日是周几'--返回值 1-星期日，2-星期一，3-星期二......7-星期六 /*年度计算*/ ,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初' ,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末' ,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初' ,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末' ,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初' ,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末' /*季度计算*/ ,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初' ,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末' ,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初' ,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末' ,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初' ,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'