Calculate hours between two dates

[Anonymous]

Hi all,

I want to know the numbers of hours between two dates.

date_creation_h	date_prise_charge_h
02/01/2014 23:14	06/01/2014 09:37
03/01/2014 16:24	06/01/2014 09:35
09/01/2014 03:32	09/01/2014 09:56
11/01/2014 01:05	13/01/2014 10:11
15/01/2014 07:11	15/01/2014 09:44
16/01/2014 16:42	16/01/2014 18:11

In my script :

Main :

SET TimeFormat='hh:mm';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm';

At the loading of my data :

Time(([Date clôture  ] - [Date de prise en charge  ]),'hh:mm') as tps_traitement

This doesn't work, if somebody have an idea!

Thanks.

Emilien

[PrashantSangle]

Hi,

Use Interval()

like interval(dateField2-dateField1,'hh:mm')

Regards

[Anonymous]

Or to return hours only using 'hh' instead of 'hh:mm'

[JonnyPoole · Accepted solution]

i used this script with your data

SET TimeFormat='hh:mm';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm';

Load

  Interval( Timestamp(date_creation_h) - Timestamp(date_prise_charge_h)) as Hours,

  Timestamp(date_creation_h) as date_creation_h,

  Timestamp(date_prise_charge_h) as date_prise_charge_h;

LOAD * INLINE [

    date_creation_h, date_prise_charge_h

    02/01/2014 23:14, 06/01/2014 09:37

    03/01/2014 16:24, 06/01/2014 09:35

    09/01/2014 03:32, 09/01/2014 09:56

    11/01/2014 01:05, 13/01/2014 10:11

    15/01/2014 07:11, 15/01/2014 09:44

    16/01/2014 16:42, 16/01/2014 18:11

];

To get this result. Does it work ?

[MarcoWedel]

(date_prise_charge_h-date_creation_h)*24

[Anonymous]

This syntax work also :

date_creation_h, date_prise_charge_h,

interval(date#(date_prise_charge_h, 'DD/MM/YYYY hh:mm') - date#(date_creation_h, 'DD/MM/YYYY hh:mm'), 'hh') as h1,

date#(date_prise_charge_h, 'DD/MM/YYYY hh:mm') - date#(date_creation_h, 'DD/MM/YYYY hh:mm') as h2

Thank you all