关于SQL 数据库的日期时间函数

select GETDATE() as '当前日期',
DateName(year,GetDate()) as '年', 取出当前年份、月、日等时间
DateName(month,GetDate()) as '月',
DateName(day,GetDate()) as '日',
DateName(dw,GetDate()) as '星期',
DateName(week,GetDate()) as '周数',
DateName(hour,GetDate()) as '时',
DateName(minute,GetDate()) as '分',
DateName(second,GetDate()) as '秒'

运行结果：

2017-08-13 23:07:15.403 2017 08 13 星期四 33 23 7 15
--------------------------------------
select CONVERT(varchar(8),getdate(), 112) AS Expr1
显示当前年月日 如：20150101

sql server2000 时间操作
1.显示本月第一天
SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
select convert(datetime,convert(varchar(8),getdate(),120)+'01',120)

2.显示本月最后一天
select dateadd(day,-1,convert(datetime,convert(varchar(8),dateadd(month,1,getdate()),120)+'01',120))
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))

3.上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))

4.本月的第一个星期一
select DATEADD(wk,DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())),0)

5.本年的第一天
SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0)

6.本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))

7.去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))

8.本季度的第一天
SELECT DATEADD(qq,DATEDIFF(qq,0,getdate()),0) 

9.本周的星期一
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)

10.查询本月的记录
select * from tableName where DATEPART(mm, theDate) = DATEPART(mm, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())

11.查询本周的记录
select * from tableName where DATEPART(wk, theDate) = DATEPART(wk, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())

12.查询本季的记录
select * from tableName where DATEPART(qq, theDate) = DATEPART(qq, GETDATE()) and DATEPART(yy, theDate) = DATEPART(yy, GETDATE())
其中:GETDATE()是获得系统时间的函数。

13.获取当月总天数:
select DATEDIFF(dd,getdate(),DATEADD(mm, 1, getdate()))

select datediff(day,
dateadd(mm, datediff(mm,'',getdate()), ''),
dateadd(mm, datediff(mm,'',getdate()), '1900-02-01'))

14.获取当前为星期几
DATENAME(weekday, getdate())

15. 当前系统日期、时间
select getdate()

16. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值
例如：向日期加上2天
select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000

17. datediff 返回跨两个指定日期的日期和时间边界数。
select datediff(day,'2004-09-01','2004-09-18') --返回：17

18. datepart 返回代表指定日期的指定日期部分的整数。
SELECT DATEPART(month, '2004-10-15') --返回 10
年为year,月为month，日为day,小时hour,分为minute，秒为second

19. datename 返回代表指定日期的指定日期部分的字符串
SELECT datename(weekday, '2004-10-15') --返回：星期五

20. day(), month(),year() --可以与datepart对照一下
select 当前日期=convert(varchar(10),getdate(),120),当前时间=convert(varchar(8),getdate(),114)
select datename(dw,'2004-10-15')
select 本年第多少周=datename(week,'2004-10-15'),今天是周几=datename(weekday,'2004-10-15')

--提取上个月的第一天到最后一天
select   convert(varchar(100),dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())),23)
select   convert(varchar(100),dateadd(dd,-day(getdate()),getdate()),23)

 -------------------------------------------

函数 参数/功能
GetDate( ) 返回系统目前的日期与时间
DateDiff (interval,date1,date2) 以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1
DateAdd (interval,number,date) 以interval指定的方式，加上number之后的日期
DatePart (interval,date) 返回日期date中，interval指定部分所对应的整数值
DateName (interval,date) 返回日期date中，interval指定部分所对应的字符串名称

举例： 
1.GetDate() 用于sql server :select GetDate()

2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒
  DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天

3.DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)
  DatePart('d','2005-7-25 22:56:32')返回值为 25即25号
  DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天
  DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年

应用示例：

查询某个日期之间的记录数据：
select * from 表 where 开始时间>'2005-02-01' and 结束时间<='2005-06-05'order by id desc

查询最近30内的记录数据：
select * from 表 where datediff(Dd,last_date,getdate())<=30 order by id desc

查询最近一周内的点击率大于100的记录数据：
select * from t_business_product where hit_count>100 and datediff(Dw,last_date,getdate())<=7 order by id desc

查询某一年(如2006年)的记录数据：
select * from 表 where DatePart(Yy,last_date)=2006 order by id desc
或
select * from 表 where DatePart(Year,last_date)=2006 order by id desc

如查询系统当前年份插入的一年内的数据：
select * from 表 where DatePart(Yy,getdate())=DatePart(Yy,getdate()) order by id desc

取系统日期 并将 日期 分开
select 当前日期=convert(varchar(10),dateadd(day,-1,getdate()),120),当前时间=convert(varchar(8),getdate(),114)
---------------------取 年月日
year(),month(),date()
--------------------------
取出今天是当月的第几周
 select  cast(datepart(mm,GETDATE()) as varchar(2)) + '月第'
 + cast((datepart(wk,GETDATE())
 - datepart(wk,convert(varchar(7),GETDATE(),120) + '-01') + 1) as varchar(2)) + '周'
----------------

 

 

日期转换格式

Select CONVERT(varchar(100), GETDATE(), 23): 2017-05-16
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2017 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/17
Select CONVERT(varchar(100), GETDATE(), 2): 17.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/17
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.17
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-17
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 17
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 17
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2017 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-17
Select CONVERT(varchar(100), GETDATE(), 11): 17/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 170516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2017 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2017-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2017-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/17 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2017-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2017-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2017 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2017
Select CONVERT(varchar(100), GETDATE(), 102): 2017.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2017
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2017
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2017

Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2017
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2017
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2017 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2017
Select CONVERT(varchar(100), GETDATE(), 111): 2017/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20170516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2017 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2017-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2017-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2017-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

select CONVERT(varchar, getdate(), 120 )   2017-09-12 11:06:08

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),/'-/',/'/'),/' /',/'/'),/':/',/'/')   20170912110608
select CONVERT(varchar(12) , getdate(), 111 )     2017/09/12
select CONVERT(varchar(12) , getdate(), 112 )     20170912
select CONVERT(varchar(12) , getdate(), 102 )     2017.09.12
select CONVERT(varchar(12) , getdate(), 101 )     09/12/2017
select CONVERT(varchar(12) , getdate(), 103 )     12/09/2017
select CONVERT(varchar(12) , getdate(), 104 )     12.09.2017
select CONVERT(varchar(12) , getdate(), 105 )     12-09-2017
select CONVERT(varchar(12) , getdate(), 106 )      12 09 2017
select CONVERT(varchar(12) , getdate(), 107 )      09 12, 2017
select CONVERT(varchar(12) , getdate(), 108 )      11:06:08
select CONVERT(varchar(12) , getdate(), 109 )      09 12 2017 1
select CONVERT(varchar(12) , getdate(), 110 )      09-12-2017
select CONVERT(varchar(12) , getdate(), 113 )      12 09 2017 1
select CONVERT(varchar(12) , getdate(), 114 )      11:06:08.177

--------------------------------------------------

----------把数字改成时间显示
select   CAST(dateadd(N,300,'')as Time) as Time1

或者
select CONVERT(varchar(5),dateadd(N,300,''),8) Time2

Dateadd(H): H 代表小时
Dateadd(N): N 代表分钟
Dateadd(S)：S 代表秒

结果300 显示为 05：00  ，在一些考勤规则里面会出现类似情况。

--------取出小时/分钟的部分
=right(convert(varchar(16),rq,121),5) as rq 取出时间点如  10：50

 -------循环插入某年某月详细时间段。

declare @date1 varchar(100),@date2 varchar(100)
declare @tmp table 
(date varchar(100))
set @date1='2017'
set @date2='05'
insert  @tmp 
select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) day from 
(select @date1+'-'+@date2+'-01' day) t1, 
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2 
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like @date1+'-'+@date2+'%'  

------把以上时间在当前年属于几周及开始日期到结束日期是什么时候显示出来。

 select datename(week,dateadd(day,-1,date)) as week,convert(varchar(10),min(date),120)+'~'+convert(varchar(10),max(date),120) as day from @tmp
group by datename(week,dateadd(day,-1,date))

------时间差（精确到天、小时、分钟）

declare @yi varchar(40),@chen varchar(40)
set @yi ='2017-01-16 15:28'
set @chen =convert(varchar(16),getdate(),20)
select cast(floor(datediff(minute,@yi,@chen)/1440)as varchar)+N'天'+
       cast(floor((datediff(minute,@yi,@chen) % 1440)/60)as varchar)+N'小时'+
       cast(((datediff(minute,@yi,@chen))-
        (floor(datediff(minute,@yi,@chen)/1440)*1440)- 
        (floor((datediff(minute,@yi,@chen) % 1440)/60)*60)) as varchar)+N'分' 
      as N'时间差'

就得出当前时间与定义的时间差了