Using Bigquery , How To Use Conditional Statement Involving Dates

Using Bigquery , how to use conditional statement involving dates

Contents hide

1 Using Bigquery , how to use conditional statement involving dates

Problem Description:

I require a column which counts the number days i.e(Order Date – 01Jan2020).

Condition is -> if Order Date lies between 01Jan2020 and 31Mar2020
then (DATE_DIFF(‘2020-01-31’ ,Order Date, DAY))
else 0
Question -> how to use this condition statement in BigQuery ?

Table –
Customer ID | Order Date
298 | 2020-02-28
78 | 2020-04-02
31 | 2021-01-09
345 | 2021-09-09
74 | 2020-01-20

I tried –

if((Order Date <'2020-01-01') and (Order Date >'2020-03-31'),(DATE_DIFF('2020-01-31' ,Order Date,  DAY)
,0))

Solution – 1

Try below syntax: Once able to run it. Replace current_date with Order_Date. Hope this will work.


select 

if(current_date <'2020-01-01' and current_date >'2020-03-31' ,DATE_DIFF('2020-01-31' ,current_date,  DAY),0);

Solution – 2

Try SELECT statement with WHERE clause:

SELECT id, orderdate, DATE_DIFF('2020-01-31', orderdate, DAY) as datediff FROM `yourdataset.ordertable`
WHERE orderdate BETWEEN '2020-01-01' AND '2020-03-31';

Output:

id  orderdate   datediff
298 2020-02-28  -28
74  2020-01-20  11

CASE statement:

SELECT 
  id, 
  orderdate,
  CASE 
      WHEN (orderdate BETWEEN '2020-01-01' AND '2020-03-31') THEN DATE_DIFF('2020-01-31', orderdate, DAY)
      ELSE 0
END AS `datediff`
FROM `yourdataset.ordertable`

Output:

id  orderdate   datediff
78  02/04/20    0
298 28/02/20    -28
345 09/09/21    0
31  09/01/21    0
74  20/01/20    11

Rate this post