Date diffrent sql query

Get Help
1

Hi, I write sql statement to calculate the date different, how ever why there is no keyword “datediff” in sql to find the date different?

2

which database are you using ?
you should use native query .
Metabase does not provide the date custom columns.

3

I use query, I write sql statement

4

in sql server you have datediff() function

DATEDIFF( interval , date1 , date2 )

SELECT DATEDIFF(day, ‘2017/08/25’, ‘2011/08/25’) AS DateDiff;

https://www.w3schools.com/sql/func_sqlserver_datediff.asp

5

SELECT nation.temp_data.EMAIL AS EMAIL, nation.temp_data.NAME AS NAME, count(*) AS count, avg(nation.temp_data.PAYMENT_RECEIVED) AS avg, sum(nation.temp_data.PAYMENT_RECEIVED) AS sum,min(nation.temp_data.TRANSACTION_DATE_1) AS min_date,max(nation.temp_data.TRANSACTION_DATE_1) AS max_date,
DATEDIFF(month,max_date,min_date) AS DateDiff
FROM nation.temp_data
WHERE nation.temp_data.'Status= 'Yes' ORDER BYcountDESC,EMAILASC,NAME` ASC

max_date and min_date as column name
my code, but getting error ‘Function not found: DATEDIFF; Did you mean date_diff? at [3:1]’

6

what is your Database

7

nation.temp_data, the data come from Bigquery

8

In BıgQuery you can try ;

SELECT DATE_DIFF(DATE '2018-02-20', DATE '2018-01-15', DAY) as days_diff;
9

I try with this query

SELECT min(sales.Onplz.TRANSACTION_DATE) AS StartD,
max(sales.Onplz.TRANSACTION_DATE) AS lastD,
DATE_DIFF( DATE StartD, DATElastD, DAY) as days_diff
FROM sales.Onplz

not working, unrecognised “StartD”

10

you should try

> DATE_DIFF( min( `sales.Onplz` . `TRANSACTION_DATE` ), max( `sales.Onplz` . `TRANSACTION_DATE` ),DAY)

or you need one more select statement .

SELECT StartD,lastD,DATE_DIFF( DATE StartD , DATE lastD , DAY) as days_diff
FROM (
SELECT min( sales.Onplz . TRANSACTION_DATE ) AS StartD ,
max( sales.Onplz . TRANSACTION_DATE ) AS lastD ,
FROM sales.Onplz
) A