Difference of two dates in sql server

Question

Is there a way to distinguish between two datetime in sql server?

For example, my dates

• 2010-01-22 15:29:55.090
• 2010-01-22 15:30:09.153

So, the result should be 14.063 seconds .

Answer 1

Just a caution to add DateDiff, it counts the number of times you go past the border that you specify as your units, so there are problems if you are looking for the exact time. eg.

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

gives the answer 1 because it crossed the border from January to February, therefore, although the time span is 2 days, the datif returns 1 - it crossed 1 the date border.

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

Gives a value of 1, again, it passed the minute limit once, therefore, although it is about 14 seconds, it will be returned in one minute when using minutes as units.

Answer 2

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

Replace DATEDIFF MyUnits on MSDN

Answer 3

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

Replace day other units for which you want to get the difference, for example second , minute , etc.

Answer 4

I can mention four important MS SQL Server features that can be very useful:

1) The DATEDIFF ( ) function is responsible for calculating the differences between the two dates, the result can be " year month month dayyyear day of the week, minute second, millisecond microsecond nanosecond ", indicated by the first parameter ( datepart ):

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

2) You can use the GETDATE () function to get the actual time and calculate the difference in any date and actual date:

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

3) Another important function is DATEADD () , used to convert some value to datetime using the same datepart (with positive values) or subtracting (with negative values) to one base date:

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

4) To format the date, as you need, the CONVERT () function was created, this is not a parametric function, but you can use part of the result to format the result as you need:

 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 is calculated in seconds, and one interesting result mixing these four functions is to show the difference in the size of um hours, minutes and seconds ( hh: mm: ss ) between two dates:

 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) 

... result 00:10:38 (638s = 600s + 38s = 10 minutes and 38 seconds)

Another example:

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

Answer 5

I tried this way and it worked. I used SQL Server version 2016

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

Various functions of DATEDIFF:

 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'); 

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

Answer 6

Inside SQL Server, dates are stored as 2 integers. The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of measures after midnight, each measure is 1/300 second.

More info here

Because of this, I often find the easiest way to compare dates - just subtract them. This handles 90% of my use cases. For example,

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

When I need an answer in units other than days, I will use DateDiff .

Answer 7

There are several ways to look at date differences and more when comparing date / time. Here I use to get the difference between two dates formatted as "HH: 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) 

I used this for a computed column, but you can trivially rewrite it as a UDF or query calculation. Note that this logic rounds off fractional seconds; 00: 00.00 to 00: 00.999 is considered a zero second and is displayed as "00:00:00".

If you expect periods to be more than a few days, this code switches if necessary to the 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 

Answer 8

The following query should contain the exact data you are looking for.

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

Here is a link from MSDN for everything you can do with the latiff function. https://msdn.microsoft.com/en-us/library/ms189794.aspx

Answer 9

Well, we all know that the answer includes DATEDIFF() . But that only gives you half the result you can be. What if you want to get the results in a human-readable format, in the form of minutes and seconds between two DATETIME values?

The functions CONVERT() , DATEADD() and, of course, DATEDIFF() ideal for a more readable result that your customers can use instead of a number.

those.

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

This will give you something like:

HH: MM

If you want more precision, just increase VARCHAR() .

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

HH: MM.SS.MS

Answer 10

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

It gives you the difference between two dates in a year.

Here (2017-2011) = 6 as a result

Syntax:

 DATEDIFF(interval, date1, date2) 

Answer 11

So this is not my answer, but I just found this while searching the Internet and for such a question. This guy set up the procedure for calculating hours, minutes and seconds. Link and code:

 --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' ) 

Answer 12

 declare @dt1 datetime='2012/06/13 08:11:12', @dt2 datetime='2012/06/12 02:11:12' select CAST((@dt2-@dt1) as time(0)) 

Answer 13

PRINTED DATIFF (second, '2010-01-22 15: 29: 55.090', '2010-01-22 15: 30: 09.153')

Answer 14

 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 

Just thought I'd say that.

Answer 15

CREATE FUNCTION getDateDiffHours (@fdate AS datetime, @tdate as date-time) RETURNS varchar (50) STARTED STARTING DECLARE @cnt int DECLARE @cntDate datetime DECLARE @dayDiff int DECLARE @dayDiffWk int DECLARE @hrsDimal

 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

Answer 16

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 works best

Answer 17

Please check below trick to find the date difference between two dates.

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

where you can change your requirement as you want the difference of days, month or year or time.

Answer 18

Check DateDiff out on Books Online.

Answer 19

For me, this worked Perfect Convert (varchar (8), DATEADD (SECOND, DATEDIFF (SECOND, LogInTime, LogOutTime), 0), 114)

and the output will be HH: MM: SS, which is shown exactly in my case.

Answer 20

Use this for 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)