Convert Dates - Gregorian to Hijri - Bigquery

Question

I am looking to convert dates from my date column in my table from the Gregorian format '12-02-2023' to Hijri (Islamic Lunar Calendar) format '01-03-1445' format.

I would like to later get results only for a particular Hijri month, lets say 9.

I have tried searching but did not get any answers that are relevant in Google Bigquery context.

Standard SQL has the 130/131 format to get Hijri dates but unsure how the application is for Bigquery.

select convert(datetime, value, 131)

Answer 1

You can achieve this by using User defined functions (UDF) in BigQuery. With UDF you can write custom Javascript code and import custom libraries to achieve what you are looking for.

CREATE OR REPLACE FUNCTION test.gregorianToHijri(gregorian_date DATE) RETURNS STRING LANGUAGE js 
  OPTIONS (
    library=['gs://<bucket-name>/moment.js', 'gs://<bucket-name>/moment-hijri.js'])
AS """

  const hijriDate = moment(gregorian_date).format('iYYYY-iM-iD');
  return hijriDate;
""";

Test query:

  SELECT test.gregorianToHijri(DATE '2023-02-12') AS hijri_date;

Output:

hijri_date -> 1444-7-20

Please replace the with your own bucket name before testing the above code. If the libraries do not meet your requirements, you can customize the code within the UDF in the way you want and just call the function using SQL. You can check more about UDF and how to import Javascript libraries into your UDF through the document