get time in minutes

sam2929
sam2929 used Ask the Experts™
on
i want to get time in minutes

i have pdate 2011-04-21and ptime 2:38:23
then   sdate 2011-04-20 and stime 11:01:41

i want to get time in minutes between pdatetime and sdatetime
if negative values the null it

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Developer / Database Administrator
Commented:

Converting it to a timestamp and using the TIMESTAMPDIFF function seems the easiest to me. It's a bit tricky to use the first time, but it's pretty easy once you figure it out. (see my example below)

HTH,
DaveSlash

create table deleteme (
  startDate date,      
  startTime time,      
  endDate   date,      
  endTime   time       
);

insert into deleteme                                          
values (current date, current time, current date, current time + 2 hours);

select endDate, endTime,
       startDate, startTime,
      timestampdiff(4, 
      cast(timestamp(endDate, endTime) - 
           timestamp(startDate, startTime) as char(22))
      ) as theHours
  from deleteme;

ENDDATE     ENDTIME   STARTDATE   STARTTIME       THEHOURS
2011-05-16  18:33:10  2011-05-16  16:33:10             120

Open in new window

Dave FordSoftware Developer / Database Administrator

Commented:
Oops.

Not that it really matters much, but the label on the calcuated column should be "theMinutes", not "theHours".

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial