# Using Bigquery , how to use conditional statement involving dates

## Using Bigquery , how to use conditional statement involving dates

Contents

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
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.