Date & Time

DATE_DIFF

BigQueryBigQuery

Calculates the difference between two DATE values in the specified unit. Returns an integer value.

Tipe hasil: INT64Diperbarui: 13 Jun 2026

Syntax

SQL
DATE_DIFF(date_expression_a, date_expression_b, date_part)

Parameter

date_expression_aDATEwajib

The end DATE (minuend)

date_expression_bDATEwajib

The start DATE (subtrahend)

date_partDATE_PARTwajib

Result unit: DAY, WEEK, MONTH, QUARTER, YEAR

Contoh Penggunaan

Calculate Age

SQL
1SELECT
2  name,
3  birth_date,
4  DATE_DIFF(CURRENT_DATE(), birth_date, YEAR) as age_years,
5  DATE_DIFF(CURRENT_DATE(), birth_date, DAY) as age_days
6FROM `project.dataset.users`;

Calculate age in years and days.

Hasil
namebirth_dateage_yearsage_days
John1990-05-153312298

Days Between Orders

SQL
1SELECT
2  order_id,
3  order_date,
4  LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) as prev_order,
5  DATE_DIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date), DAY) as days_since_last
6FROM `project.dataset.orders`;

Calculate the number of days between a customer's orders.

Months in Service

SQL
1SELECT
2  employee_id,
3  hire_date,
4  DATE_DIFF(CURRENT_DATE(), hire_date, MONTH) as months_employed
5FROM `project.dataset.employees`
6WHERE status = 'active';

Calculate tenure in months.

Pertanyaan Umum tentang DATE_DIFF

Apa itu fungsi DATE_DIFF di BigQuery?
Calculates the difference between two DATE values in the specified unit. Returns an integer value. Di BigQuery, fungsi DATE_DIFF termasuk dalam kelompok fungsi yang sering digunakan untuk mengolah dan menganalisis data secara efisien.
Bagaimana cara menggunakan DATE_DIFF di BigQuery?
Gunakan sintaks berikut: DATE_DIFF(date_expression_a, date_expression_b, date_part). Pastikan argumen yang dimasukkan sudah sesuai dengan tipe data yang diharapkan.
Apa nilai yang dikembalikan oleh fungsi DATE_DIFF?
Fungsi DATE_DIFF mengembalikan nilai bertipe INT64. Pastikan tipe data hasil sudah sesuai dengan kebutuhan query atau formula kamu. Jika input mengandung nilai NULL, perilaku fungsi dapat berbeda — selalu periksa dokumentasi untuk memastikan hasilnya sesuai ekspektasi.
Apa saja parameter fungsi DATE_DIFF?
Fungsi ini memiliki 3 parameter wajib. Parameter yang digunakan: date_expression_a (DATE, wajib): The end DATE (minuend); date_expression_b (DATE, wajib): The start DATE (subtrahend); date_part (DATE_PART, wajib): Result unit: DAY, WEEK, MONTH, QUARTER, YEAR.

Fungsi Equivalen di Database Lain

MySQL

DATEDIFF

MySQL

Snowflake

DATEDIFF

Snowflake

DuckDB

DATE_DIFF

DuckDB

Fungsi Terkait

DATE_ADDDATE_SUB
Ngulik Langsung

Sudah paham DATE_DIFF? Latih langsung di browser

Latihan interaktif, langsung di browser.

Latihan Langsung →
Kembali ke BigQuery Functions