Difference of two date time in sql server

Question

Is there any way to take the difference between two datetime in sql server   For example  my dates are   2010-01-22 15 29 55 090 2010-01-22 15 30 09 153   So  the result should be 14 063 seconds

User · Answer

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   lt   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  gt  17  begin     set  nfTime    17 00 00  end  else begin     IF  fTime  lt  8          set  nfTime    08 00 00  end   IF  tTime  gt  17  begin     set  ntTime    17 00 00  end  else begin     IF  tTime  lt  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  lt   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   gt  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

User · Answer

I can mention four important functions of  MS SQL Server that can be very useful   1  The function DATEDIFF   is responsible to calculate differences between two dates  the result could be  year quarter month dayofyear day week hour minute second millisecond microsecond nanosecond   specified on the first parameter  datepart    select datediff day  1997-10-07   2011-09-11     2  You can use the function GETDATE   to get the actual time and calculate differences of some date and actual date   select datediff day  1997-10-07   getdate       3  Another important function is DATEADD    used to convert some value in datetime using the same datepart of the datediff  that you can add  with positive values  or substract  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  The function CONVERT   was made to format the date like you need  it is not parametric function  but you can use part of the result to format the result like 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 be calculated in seconds and one interesting result mixing these four function is to show a formated difference 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        the result is 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

User · Answer

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 mention it

User · Answer

Please check below trick to find the date difference between two dates    DATEDIFF DAY ordr DocDate RDR1 U ProgDate  datedifff   where you can change according your requirement as you want difference of days or month or year or time

User · Answer

Ok we all know the answer involves DATEDIFF    But that gives you only half the result you may be after  What if you want to get the results in human-readable format  in terms of Minutes and Seconds between two DATETIME values   The CONVERT    DATEADD   and of course DATEDIFF   functions are perfect for a more easily readable result that your clients can use  instead of a number   i e   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 the VARCHAR     CONVERT varchar 12   DATEADD minute  DATEDIFF MINUTE  date1  date2   0   114        HH MM SS MS

User · Answer

The following query should give the exact stuff you are looking out for   select datediff second   2010-01-22 15 29 55 090     2010-01-22 15 30 09 153     Here is the link from MSDN for what all you can do with datediff function   https   msdn microsoft com en-us library ms189794 aspx

User · Answer

There are a number of ways to look at a date difference  and more when comparing date times   Here s what 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 calculated column  but you could trivially rewrite it as a UDF or query calculation   Note that this logic rounds down fractional seconds  00 00 00 to 00 00 999 is considered zero seconds  and displayed as  00 00 00    If you anticipate that periods may be more than a few days long  this code switches to D HH MM SS format when needed   ElapsedTime AS     CASE WHEN DATEDIFF S  StartDate  EndDate   gt   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

User · Answer

SELECT DATEDIFF yyyy   2011 08 25    2017 08 25   AS DateDiff   It s gives you difference between two dates in Year   Here  2017-2011  6 as a result  Syntax   DATEDIFF interval  date1  date2

User · Answer

SELECT  DATEDIFF day   2010-01-22 15 29 55 090    2010-01-22 15 30 09 153     Replace day with other units you want to get the difference in  like second  minute etc

User · Answer

declare  dt1 datetime  2012 06 13 08 11 12    dt2 datetime  2012 06 12 02 11 12   select CAST   dt2- dt1  as time 0

User · Answer

Check DateDiff out on Books Online

User · Answer

SELECT DATEDIFF  MyUnits   2010-01-22 15 29 55 090    2010-01-22 15 30 09 153     Substitute  MyUnits  based on DATEDIFF on MSDN

User · Answer

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    Different DATEDIFF Functions are   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

User · Answer

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

User · Answer

For Me This worked Perfectly Convert varchar 8  DATEADD SECOND DATEDIFF SECOND LogInTime LogOutTime  0  114   and the Output is  HH MM SS which is shown accurately in my case

User · Answer

PRINT DATEDIFF second  2010-01-22 15 29 55 090   2010-01-22 15 30 09 153

User · Answer

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

User · Answer

Internally in 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 clock ticks after midnight  each tick is 1 300 of a second    More info here  Because of this  I often find the simplest way to compare dates is to simply substract them  This handles 90  of my use cases  E g     select date1  date2  date2 - date1 as DifferenceInDays from MyTable       When I need an answer in units other than days  I will use DateDiff

User · Answer

Just a caveat to add about DateDiff  it counts the number of times you pass the boundary you specify as your units  so is subject to problems if you are looking for a precise timespan  e g   select datediff  m   20100131    20100201     gives an answer of 1  because it crossed the boundary from January to February  so even though the span is 2 days  datediff would return a value of 1 - it crossed 1 date boundary   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 boundary once  so even though it is approx 14 seconds  it would be returned as a single minute when using Minutes as the units

User · Answer

So this isn t my answer but I just found this while searching around online for a question like this as well   This guy set up a procedure to calculate hours  minutes and seconds   The link and the 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  gt   60  Begin select  Minute    Seconds 60 select  Seconds    Seconds 60  If  Minute  gt   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

[sql] Difference of two date time in sql server

Examples related to sql

Examples related to sql-server

Examples related to datetime