DATEDIFF doesn't work

Get Help
1

Hi,
I want to have the number of days between two dates, so use the SQL function DATEDIFF(enddate, firstdate), but I get this error:

image
image1231×357 12.1 KB

I tried DATEDIFF(d, enddate, firstdate) but I get the same error.

Thank you

2

Hi @Jhony
Post "Diagnostic Info" from Admin > Troubleshooting, and which database type you are querying.

3

Hi, I'll like to carry on from where this stopped.

I'm also having this issue where datediff doesn't seem to be working.
Here's the query I am trying to run on the Oracle Connection.

select 
	a.shortdescription ,a.status	,a.amount	,a.frequency
	,a.duedate	,a.lastpaiddate	,a.company_id	,ah.name
	,a.collector_id	,a.startdate,a.registrationdate,a.nextdate,a.isactive,a.enddate	,a.bankstatus
	,a.funding_account_id	,substr(b.switch_id,1,3)fundingbank_id,d.description fundingbank,b.accountnumber fundingaccount,a.receiving_account_id,substr(c.switch_id,1,3)receivingbank_id
	,e.description receivingbank,c.accountnumber receivingaccount,a.mandateformid
	,a.user_id,a.description,a.mandatetype,a.activationdate,a.vatamount,a.chargefee,a.payername,a.payermobile,a.netamount
	,to_char(a.startdate,'yyyy-mm-dd hh:mm:ss')start_date	,to_char(a.registrationdate,'yyyy-mm-dd hh:mm:ss')reg_date
	,to_char(a.enddate,'yyyy-mm-dd hh:mm:ss')end_date	,to_char(a.activationdate,'yyyy-mm-dd hh:mm:ss')activation_date
from 
	specsremita.collection a
join 
	specsremita.account b on a.funding_account_id=b.id
join 
	specsremita.account c on a.receiving_account_id=c.id
join 
	specsremita.bank d on d.id=substr(b.switch_id,1,3)
join 
	specsremita.bank e on e.id=substr(c.switch_id,1,3)
join 
	specsremita.accountholder ah on (ah.id = a.company_id)

I get the following error;

ORA-06553: PLS-306: wrong number or types of arguments in call to 'DATEDIFF'

The Start & End Dates have appropriate types assigned to them, so it shuold not be an issue of wrong type

image
image1567×157 6.15 KB

image
image1559×136 5.62 KB

Thanks

4

@oaajibade When you are using SQL, then it doesn't matter what you do in Data Model, since Metabase just sends your query directly to your database. So try running the query directly on your database, then you would get the same error, meaning the error is coming from your database not Metabase.
Look in your database documentation or the internet:
https://stackoverflow.com/questions/28406397/datediff-function-in-oracle

5

In Oracle you just subtract one date from the other.