Difference Between two date_time fields?

[Anonymous]

Hi I ahve 2 date time fields I want get difference beetween two times ..

How can i can get difference ...

my code is:

Directory;

Master:
LOAD callid,calldata as Call_Details
FROM

[qvd];
directory;
Call_Details:
LOAD TextBetween(Call_Details,'<CALLID>','</CALLID>') as "CALL ID",TextBetween(Call_Details,'<STARTDATETIME>','</STARTDATETIME>') as "START DATE",TextBetween(Call_Details,'<ENDDATETIME>','</ENDDATETIME>') as "END DATE" resident Master;
DROP Table Master;


Calc_Diff:
LOAD [CALL ID],[START DATE],[END DATE],(Timestamp([END DATE],'MM-DD-YYYY HH:mm:SS TT')-Timestamp([START DATE],'MM-DD-YYYY HH:mm:SS TT')) as 'DURATION' Resident Call_Details;
DROP Table Call_Details;

i followed this code but when i try to display duration in listbox it does not show data...

please help me anyone.

[Anonymous]

Hi ..

Actually i got the data from database..in that one field contains xml realated data in that i saparate fields.(like startdata and enddata)...please find the attachment...

[#3]

Hi,

Make sure that you are passing correct timestamp# format while taking text date into date format while loading ...

Check StartDate EndDate format before proceeding for interval calculation...

[#4]

You need to pass format like below to match,

(...,'YYYY-DD-MM hh:mm:ss:ms')

timestamp(timestamp#(TextBetween(Call_Details,'<STARTDATETIME>','</STARTDATETIME>'),'YYYY-DD-MM hh:mm:ss:ms'),'YYYY-DD-MM hh:mm:ss') as "START DATE",

like others...

[Anonymous]

hi...

Actually i got the data from database in that one of the field of dtabase is in xml formate.i saparate data from xml(like start data and end data).i want to display difference between two dates.

End date is22/06/2014 04:27:01

Start date is 22/06/2014 04:25:01

please give me solution......

[#6]

Try this now,

Calc_Diff:
LOAD [CALL ID],[START DATE],[END DATE],

Interval(Timestamp(Timestamp#([END DATE],'YYYY-DD-MM hh:mm:ss:ms'),'DD-MM-YYYY hh:mm:ss')-Timestamp(Timestamp#([START DATE],'YYYY-DD-MM hh:mm:ss:ms'),'DD-MM-YYYY hh:mm:ss'),'DD:hh:mm:ss') as 'DURATION'

Resident Call_Details;

regards,

harshal

[Anonymous]

Hi Harshal..

Even also its not working....................

[Anonymous]

When i try to appply year(startdate)  as my_year,  its also doesnot work..........all date functions also does work...............

[Anonymous]

Hi...

All date functions also does not work...

[#10]

year(startdate) It will not come because your startDate is in text format..

you need to convert it in date format from text..

Try this for year,

year(date(date#(startDate,'YYYY-DD-MM hh:mm:ss:ms'),'DD-MM-YYYY')) as year

check whether your year comes or not...

[Anonymous]

no it does not display year....