Sql server日期函数用法

原文地址：https://www.cnblogs.com/mq0036/p/9400070.html

SQL日期函数 -- CONVERT

SQL日期函数中的类型码可以为0,1,2,3,4,5,6,7,8,9,10,11,12,13,14
,20,21,22,23,24,25,100,101,102,103,104,105,106,107,108,109,100,
110,111,112,113,114,120,121,126,127,130,131

函数	结果
select CONVERT(varchar(30),getdate(),0)	09 13 2017 3:51PM
select CONVERT(varchar(30),getdate(),1)	09/13/17
select CONVERT(varchar(30),getdate(),2)	17.09.13
select CONVERT(varchar(30),getdate(),3)	13/09/17
select CONVERT(varchar(30),getdate(),4)	13.09.17
select CONVERT(varchar(30),getdate(),5)	13-09-17
select CONVERT(varchar(30),getdate(),6)	13 09 17
select CONVERT(varchar(30),getdate(),7)	09 13, 17
select CONVERT(varchar(30),getdate(),8)	15:51:54
select CONVERT(varchar(30),getdate(),9)	09 13 2017 3:51:54:550PM
select CONVERT(varchar(30),getdate(),10)	09-13-17
select CONVERT(varchar(30),getdate(),11)	17/09/13
select CONVERT(varchar(30),getdate(),12)	170913
select CONVERT(varchar(30),getdate(),13)	13 09 2017 15:51:54:550
select CONVERT(varchar(30),getdate(),14)	15:51:54:550
select CONVERT(varchar(30),getdate(),20)	2017-09-13 15:51:54
select CONVERT(varchar(30),getdate(),21)	2017-09-13 15:51:54.550
select CONVERT(varchar(30),getdate(),22)	09/13/17 3:51:54 PM
select CONVERT(varchar(30),getdate(),23)	2017-09-13
select CONVERT(varchar(30),getdate(),24)	15:51:54
select CONVERT(varchar(30),getdate(),25)	2017-09-13 15:51:54.550
select CONVERT(varchar(30),getdate(),100)	09 13 2017 3:51PM
select CONVERT(varchar(30),getdate(),101)	09/13/2017
select CONVERT(varchar(30),getdate(),102)	2017.09.13
select CONVERT(varchar(30),getdate(),103)	13/09/2017
select CONVERT(varchar(30),getdate(),104)	13.09.2017
select CONVERT(varchar(30),getdate(),105)	13-09-2017
select CONVERT(varchar(30),getdate(),106)	13 09 2017
select CONVERT(varchar(30),getdate(),107)	09 13, 2017
select CONVERT(varchar(30),getdate(),108)	15:51:54
select CONVERT(varchar(30),getdate(),109)	09 13 2017 3:51:54:550PM
select CONVERT(varchar(30),getdate(),110)	09-13-2017
select CONVERT(varchar(30),getdate(),111)	2017/09/13
select CONVERT(varchar(30),getdate(),112)	20170913
select CONVERT(varchar(30),getdate(),113)	13 09 2017 15:51:54:550
select CONVERT(varchar(30),getdate(),114)	15:51:54:550
select CONVERT(varchar(30),getdate(),120)	2017-09-13 15:51:54
select CONVERT(varchar(30),getdate(),121)	2017-09-13 15:51:54.550
select CONVERT(varchar(30),getdate(),126)	2017-09-13T15:51:54.550
select CONVERT(varchar(30),getdate(),127)	2017-09-13T15:51:54.550
select CONVERT(varchar(30),getdate(),130)	22 ?? ????? 1438 3:51:54:550P
select CONVERT(varchar(30),getdate(),131)	22/12/1438 3:51:54:550PM

---

其中，21,25,121效果相同（2017-09-13 15:51:54.550 ）。
其中，在select语句中需要获取固定格式日期时，只需将getdate()替换成需要查找的字段名，需要设置的该字段的别名直接在后面添加即可，

如：select CONVERT(varchar(30), RQ_REQ_DATE, 21) from td.REQ;

查询结果：

 

出处：https://blog.csdn.net/Amanda_Yt/article/details/77968559

==================================================================

另外在提供一个和日期相关的函数，方便大家参考。

 

getdate()函数：取得系统当前的日期和时间。返回值为datetime类型的。

用法：getdate()

例子：

select getdate() as dte,dateadd(day,-1,getdate()) as nowdat

 

datepart()函数：以整数的形式返回时间的指定部分。

用法：datepart(datepart,date)

参数说明：datepart时要返回的时间的部分，常用取值year、month、day、hour、minute、second

date是所指定的时间。

例子：

select datepart(month, getdate()) as'Month Number'

 

dateadd()函数：通过给指定的时间的指定部分加上一个整数值以返回一个新时间值。

用法：dateadd(datepart,number,date)

参数说明：datepart(同上)

date(同上)

number要增加的值，整型，可正可负，正值返回date之后的时间值，负值返回date之前的时间值

例子：

select getdate() as today

select dateadd(day,-1,getdate())

select dateadd(day,1,getdate())

 

datediff()函数：返回两个时间以指定时间部分来计算的差值。返回整数值。

如1991-6-12和1991-6-21之间以天来算相差9天,1998-6-12和1999-6-23按年算相差1年，1999-12-1和1999-3-12按月算相差9个月

用法：datediff(darepart,date1,date2)

参数说明：datepart（同上）

date1、date2(同上date)

例子：

select datediff(month,'1991-6-12','1992-6-21') as a

 

常用时间函数：

使用时间和日期的函数

getdate():获取系统当前时间
dateadd(datepart,number,date):计算在一个时间的基础上增加一个时间后的新时间值,比如：dateadd(yy,30,getdate())
datediff(datepart,startdate,enddate):计算两个时间的差值,比如：datediff(yy,getdate(),'2008-08-08')
dataname(datepart,date):获取时间不同部分的值，返回值为字符串
datepart(datepart,date):和datename相似，只是返回值为整型
day(date):获取指定时间的天数
month(date):获取指定时间的月份
year(date):获取指定时间的年份

 

 

SQL Server的几个分页解决方案：

解决方案1：
declare @pageIndex int,@pageSize int,@recordNum int
set @pageIndex=3
set @pageSize=3
select top(@pageSize) * from Grade where id not in (select top 
((@pageIndex-1)*@pageSize) id from Grade)//显示出第 7 到 第 9 条 

select @recordNum=count(*) from Grade//显示总记录数 

点评：效率不高，而且取出来的数据是按id排序的。如果想按其他字段排序就不行。

 

解决方案二：
declare @id int
--set rowcount 3
--select @id=id from Grade
select top 3 @id=id from Grade//这句等效于上面注视的两句
select top 3 * form Grade where id>@id//查询出第 4 到第 6 条记录 

点评：效率比方案一稍高，但是取出来的数据是也按id排序的。如果想按其他字段排序就不行

 

解决方案三：
create table #table(new_id int identity(1,1) primary key,id int)
insert into #table(id) select id from grade
select a.* from grade a join #table b on (b.new_id between 6 and 9) and a.id=b.id
//取出第 6 到第 9 条，
点评：其原理是把要分页的表的主键插入到临时表中，临时表的字段就是一个标识列和要分
页的表的主键列（包括是多个主键的情况，上面的两种方法就不行）。特点是效率高，取出
来的数据能根据自己要求的字段排序。

 

临时表：
上面的解决方案三就是使用了临时表，它与永久表相似，但临时表存储在 tempdb 中，当不
再使用时会自动删除。
临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表
的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server
实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户
都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。

 

表级变量：
象上面分页的例子也可以使用表级变量，象下面这样： 

declare @table table (newid int identiey(1,1),id int)
insert into @table(id) select id from grade
select a.* from grade a join @table b on (b.new_id between 6 and 9) and a.id=b.id
这种方式比使用临时表分页效率更高。 

 

出处：https://www.cnblogs.com/Sea1ee/p/5991273.html

===============================================================

sql获取当天零点

 SQL Server

-- 获取当天零点
select convert(varchar(10), getdate(), 120)+' 00:00:00'

 

Oracle

-- 取得当天0时0分0秒
select TRUNC(SYSDATE) FROM dual;

-- 取得当天23时59分59秒(在当天0时0分0秒的基础上加1天后再减1秒)
SELECT TRUNC(SYSDATE)+1-1/86400 FROM dual;

-- 取得当前日期是一个星期中的第几天,注意：星期日是第一天
select to_char(sysdate,'D'),to_char(sysdate,'DAY') from dual;

-- 在oracle中如何得到当天月份的第一天和最后一天
select to_char(sysdate,'yyyy-mm')||'-01' firstday,to_char(last_day(sysdate),'yyyy-mm-dd') lastday from dual

-- 实现当天16点前数据减去昨天16点的，过了16点减去今天16点
select field1, field2 from mytable
where time = decode(sign(sysdate-trunc(sysdate)-16/24), --时间判断
-1,trunc(sysdate)-1+16/24, --小于16点取昨天16点
trunc(sysdate)+16/24) --大于16点取当天16点

 

 

出处：https://www.cnblogs.com/zh-1721342390/p/10723398.html