Today() function failing during load

[#1]

Hi,

I finding the following problem very strange, I have the following statement in loading a table, for records which has greater INVOICE_DUE_DATE  than of today, the expression is failing for some reason, even  Today_date is coming as null and Invoice_Ageing_Due_Date_Days as null.

Please help.

	Today() as Today_date,
	if (INVOICE_DUE_DATE > Today(),-1, Interval(Today() - INVOICE_DUE_DATE,'D'))  as Invoice_Ageing_Due_Date_Days,
	if( Today() < INVOICE_DUE_DATE ,'Not Due',
	if(Interval(Today() - INVOICE_DUE_DATE,'D') <= 30,'1-30',
	if(Interval(Today() - INVOICE_DUE_DATE,'D') <= 60, '31-60',
	if(Interval(Today() - INVOICE_DUE_DATE,'D') <= 90,'61-90','Above 90 Days'
	))))
	as Invoice_Ageing_Due_Date,

[#2]

Hi,

Try wrapping your [INVOICE_DUE_DATE] in the Date() function like;

Date([INVOICE_DUE_DATE])

My guess is that the data types of today() and [INVOICE_DUE_DATE] are different.

Also, it looks as though you're trying to figure out the days difference using the interval() function. I'd advise wrapping the [INVOICE_DUE_DATE] with Floor() to ensure it shows from the beginning of the day like so;

Interval(Date(Today()) - Date(Floor(INVOICE_DUE_DATE)),'D')

Regards,

Nick

[er_mohit]

see the attached file

[#4]

Thanks, The strange part is that function perform right when date is before the current date.

I even tried to use the expression Today() < INVOICE_DUE_DATE to avoid such case, but in that case it failed, moreover the statement

Today() as Today_date,

is a totally a separate statement but that is also returning null for cases like where date is greater than current date.

I also tried floor() and date() but no avail.

[#5 · Accepted solution]

Problem solved when the data is loaded from QVD instead of Excel. Strange