Różnica dwóch dat i godzin na serwerze sql

Czy istnieje sposób, aby wziąć różnicę między dwoma datetimena serwerze sql?

Na przykład moje daty to

1. 2010-01-22 15:29:55.090
2. 2010-01-22 15:30:09.153

Tak więc wynik powinien być 14.063 seconds.

Jedyne zastrzeżenie do dodania o DateDiff, liczy, ile razy przekroczysz granicę, którą określisz jako swoje jednostki, więc istnieje problem, jeśli szukasz dokładnego przedziału czasu. na przykład

select datediff (m, '20100131', '20100201')

daje odpowiedź 1, ponieważ przekroczył granicę od stycznia do lutego, więc chociaż rozpiętość wynosi 2 dni, datediff zwróci wartość 1 - przekroczyła 1 granicę daty.

select datediff(mi, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')

Daje wartość 1, ponownie, przekroczył granicę minut jeden raz, więc nawet jeśli jest to około 14 sekund, zostanie zwrócony jako jedna minuta, jeśli użyjesz minut jako jednostek.

SELECT DATEDIFF (MyUnits, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')

Zastąp „MyUnits” na podstawie DATEDIFF w witrynie MSDN

SELECT  DATEDIFF(day, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')

Wymień daysię z innymi jednostkami chcesz uzyskać różnicę, jak second, minuteetc.

Mogę wymienić cztery ważne funkcje serwera MS SQL, które mogą być bardzo przydatne:

1) Funkcja DATEDIFF () jest odpowiedzialna za obliczanie różnic między dwiema datami, wynikiem może być „ rok kwartał miesiąc dzień roku dzień tydzień godzina minuta sekunda milisekunda mikrosekunda nanosekunda ”, określony w pierwszym parametrze (część daty ):

select datediff(day,'1997-10-07','2011-09-11')

2) Możesz użyć funkcji GETDATE (), aby uzyskać rzeczywisty czas i obliczyć różnice między pewną datą a rzeczywistą datą:

select datediff(day,'1997-10-07', getdate() )

3) Inną ważną funkcją jest DATEADD () , używana do konwersji pewnej wartości w datetime przy użyciu tej samej części daty z daty, którą można dodać (z wartościami dodatnimi) lub odjąć (z wartościami ujemnymi) do jednej daty bazowej:

select DATEADD(day,  45, getdate()) -- actual datetime adding 45 days
select DATEADD(  s,-638, getdate()) -- actual datetime subtracting 10 minutes and 38 seconds

4) Funkcja CONVERT () została utworzona w celu sformatowania daty tak, jak potrzebujesz, nie jest to funkcja parametryczna, ale możesz użyć części wyniku do sformatowania wyniku tak, jak potrzebujesz:

select convert(  char(8), getdate() ,   8) -- part hh:mm:ss of actual datetime
select convert(  varchar, getdate() , 112) -- yyyymmdd
select convert( char(10), getdate() ,  20) -- yyyy-mm-dd limited by 10 characters

DATETIME cold można obliczyć w sekundach, a jednym interesującym wynikiem zmieszania tych czterech funkcji jest pokazanie sformatowanej różnicy um godzin, minut i sekund ( hh: mm: ss ) między dwiema datami:

declare  @date1 datetime, @date2 datetime
set @date1=DATEADD(s,-638,getdate())
set @date2=GETDATE()

select convert(char(8),dateadd(s,datediff(s,@date1,@date2),'1900-1-1'),8)

... wynik to 00:10:38 (638s = 600s + 38s = 10 minut i 38 sekund)

Inny przykład:

select distinct convert(char(8),dateadd(s,datediff(s, CRDATE , GETDATE() ),'1900-1-1'),8) from sysobjects order by 1

Próbowałem w ten sposób i zadziałało. Użyłem SQL Server w wersji 2016

SELECT DATEDIFF(MILLISECOND,'2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')/1000.00;

Różne funkcje DATEDIFF to:

SELECT DATEDIFF(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Ref: https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

Ok, wszyscy wiemy, że odpowiedź dotyczy DATEDIFF(). Ale to daje tylko połowę rezultatu, do którego możesz dążyć. A co, jeśli chcesz uzyskać wyniki w formacie czytelnym dla człowieka, pod względem minut i sekund między dwiema DATETIMEwartościami?

Te CONVERT(), DATEADD()i oczywiście DATEDIFF()funkcje są idealne dla bardziej czytelny powoduje, że klienci mogą korzystać, zamiast numeru.

to znaczy

CONVERT(varchar(5), DATEADD(minute, DATEDIFF(MINUTE, date1, date2), 0), 114) 

To da ci coś takiego:

HH: MM

Jeśli chcesz uzyskać większą precyzję, po prostu zwiększ VARCHAR().

CONVERT(varchar(12), DATEADD(minute, DATEDIFF(MINUTE, date1, date2), 0), 114) 

HH: MM.SS.MS

Wewnętrznie w SQL Server daty są przechowywane jako 2 liczby całkowite. Pierwsza liczba całkowita to liczba dat przed lub po dacie bazowej (1900/01/01). Druga liczba całkowita przechowuje liczbę tyknięć zegara po północy, każdy tyknięcie to 1/300 sekundy.

Więcej informacji tutaj

Z tego powodu często najprostszym sposobem porównywania dat jest po prostu ich odjęcie. To obsługuje 90% moich przypadków użycia. Na przykład,

select date1, date2, date2 - date1 as DifferenceInDays
from MyTable
...

Gdy potrzebuję odpowiedzi w jednostkach innych niż dni, użyję DateDiff .

Istnieje wiele sposobów patrzenia na różnicę dat, a także więcej podczas porównywania dat / godzin. Oto, czego używam, aby uzyskać różnicę między dwiema datami sformatowanymi jako „GG: MM: SS”:

ElapsedTime AS
      RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate)        / 3600 AS VARCHAR(2)), 2) + ':'
    + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 3600 /   60 AS VARCHAR(2)), 2) + ':'
    + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) %   60        AS VARCHAR(2)), 2)

Użyłem tego dla kolumny obliczeniowej, ale można było w prosty sposób przepisać ją jako UDF lub obliczenie zapytania. Zauważ, że ta logika zaokrągla w dół ułamki sekund; 00: 00.00 do 00: 00.999 jest traktowane jako zero sekund i wyświetlane jako „00:00:00”.

Jeśli przewidujesz, że okresy mogą być dłuższe niż kilka dni, w razie potrzeby ten kod przełącza się na format D: HH: MM: SS:

ElapsedTime AS
    CASE WHEN DATEDIFF(S, StartDate, EndDate) >= 359999
        THEN
                          CAST(DATEDIFF(S, StartDate, EndDate) / 86400        AS VARCHAR(7)) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 86400 / 3600 AS VARCHAR(2)), 2) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) %  3600 /   60 AS VARCHAR(2)), 2) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) %    60        AS VARCHAR(2)), 2)
        ELSE
              RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate)        / 3600 AS VARCHAR(2)), 2) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) % 3600 /   60 AS VARCHAR(2)), 2) + ':'
            + RIGHT('0' + CAST(DATEDIFF(S, StartDate, EndDate) %   60        AS VARCHAR(2)), 2)
        END

Poniższe zapytanie powinno dać dokładnie to, czego szukasz.

select datediff(second, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')

Oto link z MSDN do wszystkiego, co możesz zrobić z funkcją datediff. https://msdn.microsoft.com/en-us/library/ms189794.aspx

SELECT DATEDIFF(yyyy, '2011/08/25', '2017/08/25') AS DateDiff

Podaje różnicę między dwiema datami w roku

Tutaj (2017-2011) = 6 w rezultacie

Składnia:

DATEDIFF(interval, date1, date2)

Użyj tego do DD:MM:SS:

SELECT CONVERT(VARCHAR(max), Datediff(dd, '2019-08-14 03:16:51.360', 
         '2019-08-15 05:45:37.610')) 
       + ':' 
       + CONVERT(CHAR(8), Dateadd(s, Datediff(s, '2019-08-14 03:16:51.360', 
         '2019-08-15 05:45:37.610'), '1900-1-1'), 8) 

Więc to nie jest moja odpowiedź, ale właśnie ją znalazłem, szukając w Internecie takiego pytania. Ten facet ustalił procedurę obliczania godzin, minut i sekund. Link i kod:

--Creating Function
If OBJECT_ID('UFN_HourMinuteSecond') Is Not Null
Drop Function dbo.UFN_HourMinuteSecond
Go
Exec(
'Create Function dbo.UFN_HourMinuteSecond
(
@StartDateTime DateTime,
@EndDateTime DateTime
) Returns Varchar(10) 
As
Begin

Declare @Seconds Int,
@Minute Int,
@Hour Int,
@Elapsed Varchar(10)

Select @Seconds = ABS(DateDiff(SECOND ,@StartDateTime,@EndDateTime))

If @Seconds >= 60 
Begin
select @Minute = @Seconds/60
select @Seconds = @Seconds%60

If @Minute >= 60
begin
select @hour = @Minute/60
select @Minute = @Minute%60
end

Else
Goto Final 
End

Final:
Select @Hour = Isnull(@Hour,0), @Minute = IsNull(@Minute,0), @Seconds =               IsNull(@Seconds,0)
select @Elapsed = Cast(@Hour as Varchar) + '':'' + Cast(@Minute as Varchar) + '':'' +     Cast(@Seconds as Varchar)

Return (@Elapsed)
End'
)

declare @dt1 datetime='2012/06/13 08:11:12', @dt2 datetime='2012/06/12 02:11:12'

select CAST((@dt2-@dt1) as time(0))

PRINT DATEDIFF (sekunda, „2010-01-22 15: 29: 55.090”, „2010-01-22 15: 30: 09.153”)

select
datediff(millisecond,'2010-01-22 15:29:55.090','2010-01-22 15:30:09.153') / 1000.0 as Secs

result:
Secs
14.063

Pomyślałem, że o tym wspomnę.

UTWÓRZ FUNKCJĘ getDateDiffHours (@fdate AS datetime, @ tdate jako 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'

KONIEC

Sol-1:

select 
  StartTime
  , EndTime
  , CONVERT(NVARCHAR,(EndTime-StartTime), 108) as TimeDiff 
from 
  [YourTable]

Sol-2:

select 
  StartTime
  , EndTime
  , DATEDIFF(hh, StartTime, EndTime)
  , DATEDIFF(mi, StartTime, EndTime) % 60 
from 
  [YourTable]

Sol-3:

select 
  DATEPART(hour,[EndTime]-[StartTime])
  , DATEPART(minute,[EndTime]-[StartTime]) 
from 
  [YourTable]

Datepart działa najlepiej

Sprawdź poniższą sztuczkę, aby znaleźć różnicę dat między dwiema datami

 DATEDIFF(DAY,ordr.DocDate,RDR1.U_ProgDate) datedifff

gdzie możesz zmienić według swoich wymagań, jak chcesz, różnicę dni, miesiąca, roku lub czasu.

Dla mnie to zadziałało idealnie Konwertuj (varchar (8), DATEADD (SECOND, DATEDIFF (SECOND, LogInTime, LogOutTime), 0), 114)

a wyjście to HH: MM: SS, co jest dokładnie pokazane w moim przypadku.

Sprawdź DateDiff out on Books Online.