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?
which database are you using ?
you should use native query .
Metabase does not provide the date custom columns.
I use query, I write sql statement
in sql server you have datediff() function
DATEDIFF( interval , date1 , date2 )
SELECT DATEDIFF(day, ‘2017/08/25’, ‘2011/08/25’) AS DateDiff;
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 BY
countDESC,
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]’
what is your Database
nation.temp_data, the data come from Bigquery
In BıgQuery you can try ;
SELECT DATE_DIFF(DATE '2018-02-20', DATE '2018-01-15', DAY) as days_diff;
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”
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
, DATElastD
, DAY) asdays_diff
FROM (
SELECT min(sales.Onplz
.TRANSACTION_DATE
) ASStartD
,
max(sales.Onplz
.TRANSACTION_DATE
) ASlastD
,
FROMsales.Onplz
) A