Sql как найти разницу в датах

CREATE FUNCTION getDateDiffHours(@fdate AS datetime,@tdate as datetime)
RETURNS varchar (50)
AS
BEGIN
    DECLARE @cnt int
    DECLARE @cntDate datetime
    DECLARE @dayDiff int
    DECLARE @dayDiffWk int
    DECLARE @hrsDiff decimal(18)
    
    DECLARE @markerFDate datetime
    DECLARE @markerTDate datetime
    
    DECLARE @fTime int
    DECLARE @tTime int 
    
    
    DECLARE @nfTime varchar(8)
    DECLARE @ntTime varchar(8)
    
    DECLARE @nfdate datetime
    DECLARE @ntdate datetime
    
    -------------------------------------
    --DECLARE @fdate datetime
    --DECLARE @tdate datetime

    --SET @fdate = '2005-04-18 00:00:00.000'
    --SET @tdate = '2005-08-26 15:06:07.030'
    -------------------------------------
    
    DECLARE @tempdate datetime
    
    --setting weekends
    SET @fdate = dbo.getVDate(@fdate)
    SET @tdate = dbo.getVDate(@tdate)
    --RETURN @fdate 

    SET @fTime = datepart(hh,@fdate)
    SET @tTime = datepart(hh,@tdate)
    --RETURN @fTime 
    if datediff(hour,@fdate, @tdate) <= 9
    
            RETURN(convert(varchar(50),0) + ' Days ' + convert(varchar(50),datediff(hour,@fdate, @tdate)))  + ' Hours'
    else
    --setting working hours
    SET @nfTime = dbo.getV00(convert(varchar(2),datepart(hh,@fdate))) + ':' +dbo.getV00(convert(varchar(2),datepart(mi,@fdate))) + ':'+  dbo.getV00(convert(varchar(2),datepart(ss,@fdate)))
    SET @ntTime = dbo.getV00(convert(varchar(2),datepart(hh,@tdate))) + ':' +dbo.getV00(convert(varchar(2),datepart(mi,@tdate))) + ':'+  dbo.getV00(convert(varchar(2),datepart(ss,@tdate)))
    
    IF @fTime > 17 
    begin
        set @nfTime = '17:00:00'
    end 
    else
    begin
        IF @fTime < 8 
            set @nfTime = '08:00:00'
    end 
    
    IF @tTime > 17 
    begin
        set @ntTime = '17:00:00'
    end 
    else
    begin
        IF @tTime < 8 
            set @ntTime = '08:00:00'
    end 

    
    
    -- used for working out whole days

    SET @nfdate = dateadd(day,1,@fdate) 

    SET @ntdate = @tdate
    SET @nfdate = convert(varchar,datepart(yyyy,@nfdate)) + '-' + convert(varchar,datepart(mm,@nfdate)) + '-' + convert(varchar,datepart(dd,@nfdate))
    SET @ntdate = convert(varchar,datepart(yyyy,@ntdate)) + '-' + convert(varchar,datepart(mm,@ntdate)) + '-' + convert(varchar,datepart(dd,@ntdate))
    SET @cnt = 0
    SET @dayDiff = 0 
    SET @cntDate = @nfdate
    SET @dayDiffWk = convert(decimal(18,2),@ntdate-@nfdate)
    
    --select @nfdate,@ntdate

    WHILE @cnt < @dayDiffWk
    BEGIN   
        IF (NOT DATENAME(dw, @cntDate) = 'Saturday') AND (NOT DATENAME(dw, @cntDate) = 'Sunday')
        BEGIN 
            SET @dayDiff = @dayDiff + 1
        END 
        SET @cntDate = dateadd(day,1,@cntDate)
        SET @cnt = @cnt + 1
    END 
    
    --SET @dayDiff = convert(decimal(18,2),@ntdate-@nfdate) --datediff(day,@nfdate,@ntdate)
    --SELECT @dayDiff
    
    set @fdate = convert(varchar,datepart(yyyy,@fdate)) + '-' + convert(varchar,datepart(mm,@fdate)) + '-' + convert(varchar,datepart(dd,@fdate)) + ' ' + @nfTime
    set @tdate = convert(varchar,datepart(yyyy,@tdate)) + '-' + convert(varchar,datepart(mm,@tdate)) + '-' + convert(varchar,datepart(dd,@tdate)) + ' ' + @ntTime
    
    set @markerFDate = convert(varchar,datepart(yyyy,@fdate)) + '-' + convert(varchar,datepart(mm,@fdate)) + '-' + convert(varchar,datepart(dd,@fdate)) + ' ' + '17:00:00'
    set @markerTDate = convert(varchar,datepart(yyyy,@tdate)) + '-' + convert(varchar,datepart(mm,@tdate)) + '-' + convert(varchar,datepart(dd,@tdate)) + ' ' + '08:00:00'
    
    --select @fdate,@tdate
    --select @markerFDate,@markerTDate
    
    set @hrsDiff = convert(decimal(18,2),datediff(hh,@fdate,@markerFDate))
    
    --select @hrsDiff
    set @hrsDiff = @hrsDiff +  convert(int,datediff(hh,@markerTDate,@tdate))

    --select @fdate,@tdate  
    
    IF convert(varchar,datepart(yyyy,@fdate)) + '-' + convert(varchar,datepart(mm,@fdate)) + '-' + convert(varchar,datepart(dd,@fdate)) = convert(varchar,datepart(yyyy,@tdate)) + '-' + convert(varchar,datepart(mm,@tdate)) + '-' + convert(varchar,datepart(dd,@tdate))  
    BEGIN
        --SET @hrsDiff = @hrsDiff - 9
        Set @hrsdiff = datediff(hour,@fdate,@tdate)
    END 
    
    --select FLOOR((@hrsDiff / 9))
    
    IF (@hrsDiff / 9) > 0 
    BEGIN
        SET @dayDiff = @dayDiff + FLOOR(@hrsDiff / 9)
        SET @hrsDiff = @hrsDiff - FLOOR(@hrsDiff / 9)*9
    END 

    --select convert(varchar(50),@dayDiff) + ' Days ' + convert(varchar(50),@hrsDiff)   + ' Hours'

    RETURN(convert(varchar(50),@dayDiff) + ' Days ' + convert(varchar(50),@hrsDiff))    + ' Hours'

END

DATEDIFF() function :
This function in SQL Server is used to find the difference between the two specified dates.

Features :

• This function is used to find the difference between the two given dates values.
• This function comes under Date Functions.
• This function accepts three parameters namely interval, first value of date, and second value of date.
• This function can include time in the interval section and also in the date value section.

Syntax :

DATEDIFF(interval, date1, date2)

Parameter :
This method accepts three parameters as given below :

• interval : It is the specified part which is to be returned. Moreover, the values of the interval can be as given below:

1. year, yyyy, yy = Year, which is the specified year.
2. quarter, qq, q = Quarter, which is the specified quarter.
3. month, mm, m = month, which is the specified month.
4. dayofyear, dy, y = Day of the year, which is the specified day of the year.
5. day, dd, d = Day, which is the specified day.
6. week, ww, wk = Week, which is the specified week.
7. weekday, dw, w = Weekday, which is the specified week day.
8. hour, hh = hour, which is the specified hour.
9. minute, mi, n = Minute, which is the specified minute.
10. second, ss, s = Second, which is the specified second.
11. millisecond, ms = Millisecond, which is the specified millisecond.

• date1, date2 : The two specified dates in order to find the difference between them.

Returns :
It returns the difference between the two specified dates.

Example-1 :
Using DATEDIFF() function and getting the difference between two values of dates, in years.

SELECT DATEDIFF(year, '2010/01/12', '2021/01/12');

Output :

11

Example-2 :
Using DATEDIFF() function and getting the difference between two values of dates, in months.

SELECT DATEDIFF(month, '2010/2/12', '2021/12/12');

Output :

142

Example-3 :
Using DATEDIFF() function and getting the negative difference between the two values of dates, in day.

SELECT DATEDIFF(day, '2021/2/1', '2010/12/12');

Output :

-3704

Example-4 :
Using DATEDIFF() function and getting the difference between the two values of dates which includes time as well, in hour.

SELECT DATEDIFF(hour, '2019/2/1 09:55', '2020/12/12 07:45');

Output :

16318

Example-5 :
Using DATEDIFF() function and getting the difference between the two values of dates using variables which includes time as well, in second.

DECLARE @date1 VARCHAR(50);
DECLARE @date2 VARCHAR(50);
SET @date1 = '2019/2/1 09:55:44';
SET @date2 = '2020/12/12 07:45:22';
SELECT DATEDIFF(second, @date1, @date2);

Output :

58744178

Application :
This function is used to find the difference between two specified values of date.

Last Updated :
18 Jan, 2021

Like Article

Save Article