标签归档:时间及日期函数

mssql sqlserver 获取sql两个日期之间的天数差–并排除其中所包含的周末


摘要:
下文讲述使用sql函数的方法获取两个日期之间的天数差,如下所示:
实验环境:sql server 2008 R2


实现思路:
使用while循环遍历两个日期之间,并获取天数差。

     create function fn_dateRangeGet(@d1 datetime,@d2 datetime)
    returns int 
     begin
        declare @i int =0 
          if @d1 is null or @d2 is null 
          begin
             set @i =-99
             return @i 
          end

         if @d1 >@d2 
         begin
             set @i=-1 
             return @i  
         end
         
        while @d1 <@d2 
        begin
             declare @xingQiLiu   dateTime 
             declare @xingQiTian dateTime
             set @xingQiLiu  =N'2019-6-29'
             set @xingQiTian =N'2019-6-30'
     
             if  weekday(@d1)  = weekday(@xingQiLiu)   or    weekday(@d1)  = weekday(@xingQiTian) 
              begin
                   set @d1 =dateadd(day,1,@d1)           
               end
               else
               begin
                   set @d1 =dateadd(day,1,@d1)     
                   set @i= @i+1
               end
        end
         
        return @i 
     end

mssql sqlserver DATEDIFF 日期时间差值计算函数使用说明


摘要:
下文主要讲述 DATEDIFF 系统函数的使用说明,
实验环境:
sqlserver 2000
如下所示:



DATEDIFF 语法及功能简介

DATEDIFF 功能说明:
返回两个日期之间的差值,并将差值采用分钟、小时、天、年等形式返回。
———————-
DATEDIFF 语法说明:
DATEDIFF(datepart,startdate,enddate)
参数说明:
datepart:

参数名称 参数缩写 参数说明
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

startdate、enddate:
此处表达式需为一个time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 类型的值,
如果输入的为字符串,那么字符串一定要能转换为日期类型
————————–
DATEDIFF 返回值说明:
int

注意事项:
返回指定的 startdate 和 enddate 之间差,并按照datepart规定的类型转换为int。
当返回值超过int的最大数据范围,则返回相应的错误信息



DATEDIFF 举例说明

 --例1:
SELECT DATEDIFF(year, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2018-10-12 23:59:59.9999999', '2018-10-01 00:00:00.0000000');

--例2:在表中进行相关计算
  CREATE TABLE dbo.test  
    (  
    a datetime2  
    ,b datetime2  
    );  
INSERT INTO dbo.test(a,b)  
    VALUES('2018-05-06 10:09:09','2018-05-07 10:09:09');  
SELECT DATEDIFF(day,a,b) AS '间隔天数'  
FROM dbo.test   
    go;
    drop table test 
    go