Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to find working days between a date field and and a DateTime field. The following results in a zero value for DaysWork Any help would be appreciated.
ProjectTable:
LOAD *, recno() as InvID INLINE [
BegDate, EndDate
03/20/18, 03/28/2018 12:00:00
11/05/2018, 12/10/2018 13:00:00
1/4/2018, 2/5/2018 09:00:00
2/10/2018, 3/31/2018 10:00:00
03/25/18, 5/19/2018 11:00:00
09/01/2018, 9/13/2018 12:30:00
09/01/218, 9/14/2018 11:30:00
09/10/2018, 9/15/2018 14:00:00
] ;
NrDays:
Load
* ,NetworkDays(BegDate,EndDate) as DaysWork
Resident ProjectTable;
Drop table ProjectTable;
Hi,
Your two field date are different. It works if you load :
Load
* ,NetworkDays(Date#(BegDate,'MM/DD/YYYY'),floor(Timestamp#(EndDate,'MM/DD/YYYY hh:mm:ss'))) as DaysWork
Resident ProjectTable;
Drop table ProjectTable;
And take a look here about floor function
Hi
Please try this.
ProjectTable:
LOAD
DATE(DATE#(BegDate,'MM/DD/YYYY')) as BegDate,
DATE(DATE#(EndDate,'MM/DD/YYYY h:mm:ss')) as EndDate,
recno() as InvID INLINE [
BegDate, EndDate
03/20/18, 03/28/2018 12:00:00
11/05/2018, 12/10/2018 13:00:00
1/4/2018, 2/5/2018 09:00:00
2/10/2018, 3/31/2018 10:00:00
03/25/18, 5/19/2018 11:00:00
09/01/2018, 9/13/2018 12:30:00
09/01/218, 9/14/2018 11:30:00
09/10/2018, 9/15/2018 14:00:00
] ;
NrDays:
Load
* ,NetworkDays(BegDate,EndDate) as DaysWork
Resident ProjectTable;
Drop table ProjectTable;