Difference between two dates

[tmumaw]

Hi all,

I'm trying to get the number of days between 2 dates with one date being today.  I am using this in my script but getting script error.  Any idea what might be wroing?

avg(Ceil([MaxDate] - '$(vToday)')) as NoDays

[vishsaggi]

You are using avg() which is an aggregate function. If you use aggregate function in your load script you have to use Group By Clause. So like

LOAD *, avg(yourexpr) as NoDays

Resident Tablename

Group By FieldName;

[tmumaw]

Thanks.  That should work.  I have another issue though and just noticed it.  For some reason my variable for vToday is coming out as 1899-12-30 and it should be today's date.  Here is my script:  thanks for all your help.

IMRG:
Load
[Measuring point_POINT],
Max(Date([Billing Date],'YYYY-MM-DD')) as MaxDate,
Date($(vToday),'YYYY-MM-DD') as TodayDate,
Sum(NoHrs) as Hours
Resident IMRGTemp
Group by [Measuring point_POINT]; 

[johanlindell · Accepted solution]

Check the format of your vToday variable. If it is a date (2018-02-15) and not a number 43146 your formula needs to slightly changed.

If it is a date

TRACE $(vToday); returns 2018-02-15 then

Date($(vToday),'YYYY-MM-DD') as TodayDate,

Resolves to Date(2018-02-15,'YYYY-MM-DD') as TodayDate => Date(2001,'YYYY-MM-DD') as TodayDate,

Change to

Date('$(vToday)','YYYY-MM-DD') as TodayDate,

Date($(vToday),'YYYY-MM-DD') as TodayDate,

only works if TRACE $(vToday); returns 43146.

[tmumaw]

Thanks.  This one worked perfectly.

Date('$(vToday)','YYYY-MM-DD') as TodayDate