calculating date & time difference from 4 text fields

• Sjey

  SSC-Addicted

  Points: 468

  More actions

  February 8, 2007 at 8:16 am

  #116203

  I am using MS SQL Server 2000.

  I have 4 text fields

  1. event begin date

  2. event begin time

  3. event end date

  4. event end time

  I have to find the day and time difference in months days hours and  minutes between the event begin date & time and the event end date & time – The event begin date and event end date are char(8) & event begin time and event end time are char(4) – with military time. I would need to combine the event begin date & event begin time to get the event begin date  & time and the same for the event end date & time.

  If the values of the 4 text fields are

  event begin date = ‘20070207’, event begin time = ‘2015’, event end date = ‘20070208’, event end time = ‘0105’, the difference should be 0 month 0 day 4 hours 50 minutes.

  Can somebody please help with MS SQL Server 2000 syntax?

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  February 8, 2007 at 8:52 am

  #688600

  This is a bit long-winded and there may be a better way (you could also make this into a function) but:

  DECLARE @beginDate VARCHAR(8)

  DECLARE @beginTime VARCHAR(8)

  DECLARE @finalDate VARCHAR(8)

  DECLARE @finalTime VARCHAR(8)

  SET @beginDate = '20070207'

  SET @beginTime = '0000'

  SET @finalDate = '20070208'

  SET @finalTime = '0705'

  DECLARE @beginDateTime DATETIME

  DECLARE @finalDateTime DATETIME

  DECLARE @minDiff INT

  DECLARE @result VARCHAR(100)

  SET @beginTime = LEFT(@beginTime,2) + ':' + SUBSTRING(@beginTime,3,2) + ':' + '00'

  SET @finalTime =  LEFT(@finalTime,2) + ':' + SUBSTRING(@finalTime,3,2) + ':' + '00'

  SET @beginDateTime = CONVERT(datetime, @beginDate, 112) + CONVERT(datetime, @beginTime, 108)

  SET @finalDateTime = CONVERT(datetime, @finalDate, 112) + CONVERT(datetime, @finalTime, 108)

  SELECT @minDiff = DATEDIFF(mi, @beginDateTime, @finalDateTime)

  SET @result = ''

  IF @minDiff > (60*24)

  BEGIN

   SET @result = @result + CAST((@minDiff / 60/ 24) AS VARCHAR) + ' Days, '

   SET @minDiff = @minDiff - (ROUND((@minDiff /60/24), 0, 1) * 60 * 24)

  END

  ELSE

   SET @result = @result + '0 Days, '

  IF @minDiff > 60

  BEGIN

   SET @result = @result + CAST((@minDiff / 60) AS VARCHAR) + ' Hours, '

   SET @minDiff = @minDiff - (ROUND((@minDiff / 60), 0, 1) * 60 )

  END

  ELSE

   SET @result = @result + '0 Hours, '

  IF @minDiff > 0

  BEGIN

   SET @result = @result + CAST(ISNULL(@minDiff,0) AS VARCHAR) + ' Minutes'

   SET @minDiff = @minDiff - ROUND(@minDiff, 0, 1)

  END

  SELECT @result

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]

• Ken McKelvey

  SSCoach

  Points: 18895

  More actions

  February 8, 2007 at 9:18 am

  #688611

  If possible, change the dates and times to use datetime.

  Also, how are you going to define a month? For days, hours and minutes the following should work:

  SELECT D.MiDiff/1440 AS Days

          ,D.MiDiff%1440/60 AS Hours

          ,D.MiDiff%1440%60 AS Minutes

  FROM ( SELECT DATEDIFF(mi, BeginDate + ' ' + LEFT(BeginTime, 2)

                                  + ':' + RIGHT(BeginTime, 2)

                          ,EndDate + ' ' + LEFT(EndTime, 2)

                                  + ':' + RIGHT(EndTime, 2))

          FROM YourTable) D (MiDiff)

• vadba

  SSChampion

  Points: 11133

  More actions

  February 9, 2007 at 7:38 am

  #688831

  Here's Ken's version, which works quite well, using Stuff() instead of Left() and Right():

  SELECT D.MiDiff/1440 AS Days

  ,D.MiDiff%1440/60 AS Hours

  ,D.MiDiff%1440%60 AS Minutes

  FROM ( SELECT DATEDIFF(mi, BeginDate + ' ' + Stuff(BeginTime,3,0,':')

  , EndDate + ' ' + Stuff(EndTime,3,0,':'))

  FROM YourTable) D (MiDiff)