Help With Date/Time Query

• K Currie

  SSCommitted

  Points: 1662

  More actions

  September 7, 2006 at 7:56 pm

  #62706

  Hi, I need help with a date/time query.

  I have four fields, startdate, starttime, enddate, endtime. I need to calculate the difference between the startdate/time and the enddate/time. For example, 09/07/2006 10:00AM and 09/07/2006 11:00AM is 60 minutes. I can't figure out how to phrase this.

  declare @startdate datetime

  declare @starttime datetime

  declare @enddate datetime

  declare @endtime datetime

  set @startdate = '09/07/2006'

  set @starttime = '10:00AM'

  set @enddate = '09/07/2006'

  set @endtime = '11:00AM'

  declare @start datetime

  declare @end datetime

  set @start = @startdate + @starttime

  set @end = @enddate + @endtime

  declare @minutes

  set @minutes = @end-@start

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 7, 2006 at 8:59 pm

  #659248

  Hope this one solves your problem.

  declare @startdate datetime

  declare @starttime datetime

  declare @enddate datetime

  declare @endtime datetime

  Declare @startdatetime datetime,@enddatetime datetime

  set @startdate = '09/07/2006'

  set @starttime = '10:00AM'

  set @enddate = '09/07/2006'

  set @endtime = '11:20AM'

  Select @startdatetime = DateAdd(ss,DatePart(ss,@starttime),DateAdd(mi,DatePart(mi,@starttime),DateAdd(hh,DatePart(hh,@starttime),@startdate)))

  Select @enddatetime = DateAdd(ss,DatePart(ss,@endtime),DateAdd(mi,DatePart(mi,@endtime),DateAdd(hh,DatePart(hh,@endtime),@enddate)))

  select @startdatetime,@enddatetime,Datediff(mi,@startdatetime,@enddatetime)

  Thanks

  Sreejith

• Juergen Kaphengst

  Newbie

  Points: 2

  More actions

  September 8, 2006 at 12:58 am

  #659275

  Hi together,

  i wonder if you have considered using the SQL Datediff() function? Look it up in help online, it offers convenient access to what you may need.

  Cheers,

  Juergen

• vadba

  SSChampion

  Points: 11133

  More actions

  September 8, 2006 at 8:55 am

  #659360

  Just change the last line to:

  set @minutes = DateDiff(mi, @start, @end)

• K Currie

  SSCommitted

  Points: 1662

  More actions

  September 8, 2006 at 4:37 pm

  #659480

  Thanks to all for the suggestions.

  I got my query to work!!