Need help in Teradata BTEQ - Convert Islamic (Hijri) date to Georgrian Date

General

Need help in Teradata BTEQ - Convert Islamic (Hijri) date to Georgrian Date

Need help in Teradata BTEQ - Convert Islamic (Hijri) date to Georgrian Date 

5 REPLIES
Senior Apprentice

Re: Need help in Teradata BTEQ - Convert Islamic (Hijri) date to Georgrian Date

Hi,

To be clear about this, I don't think the conversion will be done by any BTEQ functionality, it will require SQL - which may be executed using BTEQ.

 

Disclaimer - I am not a Muslim so please accept my apologies if the following is incorrect.

From a bit of reading, converting from the Hijri calendar to Gregorian also depends on which version of the calendar you're using.

If  using the "The Tabular Islamic Calendar" (see https://www.timeanddate.com/date/islam-leap-year.html) then I think it should be possible to calculate the relevant conversion. The above page includes the following:

   To make Islamic time reckoning more predictable and less dependent on lunar observations, Muslim scholars developed the Tabular Islamic calendar in the 8th century CE. This system uses arithmetical rules to determine the length of each month and inserts leap days on a regular basis.
   Like in the traditional version, each year has 12 months. However, their length is predetermined: months with uneven numbers have 30 days, while months with even numbers have 29 days.
   In a leap year, a day is added to the 12th and final month, Dhu 'l-Hidjdja, making it 30 days long. Common years in the Tabular Islamic calendar have 354 days, while leap years are 355 days long.
Leap Years Rules
A leap day is added every 2 to 3 years. There are 11 leap years in a 30-year cycle. Their distribution varies slightly from one country or Muslim community to another. However, the most common version defines the 2nd, 5th, 7th, 10th, 13th, 16th, 18th, 21st, 24th, 26th, and 29th year of each 30-year cycle as leap years.

 

We'll also probably need to know the format of the data being passed into or stored in Teradata. Is this character data? Is it numeric data?

Can you give some examples of data values from the Hijri calendar and the required Gregorian date values?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Need help in Teradata BTEQ - Convert Islamic (Hijri) date to Georgrian Date

Thanks for your Reply!

 

From the Flat file, We do fast load to store the data as Varchar(255) in Teradata Staging tables. Requirement now it to convert the varchar value (Hijri Date) stored in Staging tables to Geogrian date in Teradata ODS table

Example Hijri Varchar value as 1437/12/12,1437/10/12

Senior Apprentice

Re: Need help in Teradata BTEQ - Convert Islamic (Hijri) date to Georgrian Date

Hi,

Three questions:

I assume those values are year/month/day?

What gregorian calendar dates do they represent?

Are you using the "The Tabular Islamic Calendar"?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Need help in Teradata BTEQ - Convert Islamic (Hijri) date to Georgrian Date

There are multiple variations of the Islamic calendar, sometimes a calculation formula exists, sometimes not.

 

I would do either

- get a list of Hijri/Gregorian dates from a reliable source (or create it using Perl, etc.) and add new column(s) to your existing calendar table.

or

- get C-code and install it as a C-UDF

 

Btw, a great site with calendar information is Claus Tondering's Calendar FAQ

Senior Apprentice

Re: Need help in Teradata BTEQ - Convert Islamic (Hijri) date to Georgrian Date

Hi Praburaja,

 

As has been mentioned there are many Islamic calendars in use. Your company needs to decide which one should be used for this conversion.

 

As Dieter said if the one you use does not have a formula then your best bet is to create (or add columns to) a calendar table such that your code can join on the Islamic date and return a Gregorian date.

 

If a formula exists then you can write some code. As a possible solution for you (or at least a partial one) you could use the following. Note that the code shown below has limitations:

  1. It is based on the "Tabular Islamic Calendar".
  2. It only covers a single 30 year cycle,Hijri 1410 to 1440 inclusive (@August 1989 to Sept 2018 Gregorian). Any years outside of that range will generate a NULL value for the Gregorian date.
  3. However, I have tried to structure the code so that it is easy for you to extend it if you need to.

The code is broken into two sql UDF's which need to be installed in sequence.

The first one to be installed is WA_HIJRI_DAYINYEAR which accepts a Hijri month and day number and returns the 'day number in the year'. This is used by the second UDF which is the one that your code will use.

 

REPLACE FUNCTION WA_HIJRI_DAYINYEAR(HMonth INTEGER, HDay INTEGER)
/* SQL UDF to calculate the day number if a year using the 'Tabular Islamic Calendar' (Hijri calendar).
   Based on the rules specified at: https://www.timeanddate.com/date/islam-leap-year.html
   Original code by Dave Wellman. */
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SPECIFIC WA_HIJRI_DAYINYEAR
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
   HDay 
   + ((CASE WHEN HMonth MOD 2 = 0 THEN ((HMonth/2)-1) ELSE (HMonth/2) end) * 59) 
   + (CASE WHEN HMonth MOD 2 = 1 THEN 0 ELSE 30 end)
;

 

The second UDF is WA_TIC2GREG is the one that you will use in your code. It accepts a Hijri date formatted as 'yyyy/mm/dd' (although it will work even if the 'mm' or 'dd' are 1 digit numbers) and returns a Teradata DATE column in the Gregorian calendar.

 

 

REPLACE FUNCTION WA_TIC2GREG(TICDate VARCHAR(10))
/* SQL UDF to return a Teradata date using the Gregorian calendar from a date using the 'Tabular Islamic Calendar' (Hijri calendar).
   Based on the rules specified at: https://www.timeanddate.com/date/islam-leap-year.html
   Original code by Dave Wellman. 

   Assumptions: 
   - input date is formatted as yyyy/month/day where 'month' and 'day' are 1 or 2 digit values
   - year range is 1410 - 1440 inclusive. Years outside of this range will return a NULL value.
*/
RETURNS DATE
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SPECIFIC WA_TIC2GREG
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
 (CASE
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1441 THEN NULL
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1440 THEN
     DATE '2018-09-12' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1440) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1437 THEN
     DATE '2015-10-15' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1437) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1435 THEN
     DATE '2013-11-05' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1435) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1432 THEN
     DATE '2010-12-08' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1432) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1429 THEN
     DATE '2008-01-10' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1429) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1427 THEN
     DATE '2006-01-31' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1427) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1424 THEN
     DATE '2003-03-05' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1424) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1421 THEN
     DATE '2000-06-04' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1421) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1418 THEN
     DATE '1997-05-09' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1418) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1416 THEN
     DATE '1995-05-31' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1416) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1413 THEN
     DATE '1992-07-02' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1413) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= 1410 THEN
     DATE '1989-08-04' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - 1410) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1
  ELSE NULL
  END);

 

 

I put together some test data and then produced the following query and results:

 

select hijri_date
  ,WA_TIC2GREG(hajri_date) as gregdate
from hijri_stg as a
where gregdate is not null
order by col1;

hijri_date gregdate
1437/12/12 2016-09-15
1437/10/12 2016-07-18
1410/6/3 1990-01-01
1420/9/24 2000-01-01
1431/1/15 2010-01-01
1410/7/3 1990-01-30
1438/12/17 2017-09-09
1438/1/1 2016-10-03

 

 

If you decide to use the above but need to extend it then you will need to modify WA_TIC2GREG by adding a new 'WHEN' clause at the appropriate point. Note that the WHEN clauses are in descending sequence of Hijri year number. This is important for the logic to work.

Assume that you want to extend the range of years to include Hijri year 1409. You add a new WHEN clause at the end of the existing list (because 1409 is lower than all other currently handled years).

  WHEN CAST(STRTOK(TICDate,'/',1) AS INTEGER) >= hyear THEN
     DATE 'yyyy-mm-dd' + ((CAST(STRTOK(TICDate,'/',1) AS INTEGER) - hyear) * 354) + WA_HIJRI_DAYINYEAR(CAST(STRTOK(TICDate,'/',2) AS INTEGER), CAST(STRTOK(TICDate,'/',3) AS INTEGER)) - 1

where:

  • "hyear" is the new Hijri year being handled. This goes into two places.
  • "yyyy-mm-dd" is the Gregorian date that is equivalent to the first day in the new Hijri year.

 

I hope this is useful to you.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com