Date gap

[upaliwije]

I want to create a variable to find the no of days between two dates

Date field in my Table is From_Date

I want to create a date as 31-aug-2014 and

then find difference between

31-Aug-2014-from_date

Pls help me to create variable for this

[MarcoWedel]

please post sample data and script

[MarcoWedel]

Hi,

maybe you are looking for this?:

LET vDateToCompare =  Num(Date#('31-aug-2014', 'DD-MMM-YYYY'));

// create some random From_Dates
myTable:
LOAD date(floor(FROM_DATE),'DD-MMM-YYYY')AS  FROM_DATE;
LOAD YearStart(Today())+Rand()*($(vDateToCompare)-YearStart(Today())) as FROM_DATE
AutoGenerate 100;

//add interval between vDateToCompare and FROM_DATE
Left Join (myTable)
LOAD Distinct
  FROM_DATE,
  Interval($(vDateToCompare)-FROM_DATE) as Interval,
  DayStart($(vDateToCompare))-DayStart(FROM_DATE) as DaysBetween
Resident myTable;

hope this helps

regards

Marco

[upaliwije]

Thanks all