Q

Coding for unique calendar year, part 2

What to do if the calendar starts from April 21 as the first day of the first month, and each month has 30 days?

(Assuming there are only 360 (= 12 x 30) days in a year.)

Continued from part 1.

Code for M30-Gregorian Conversion Functions

 CREATE OR REPLACE FUNCTION from_m30 ( in_text CHAR ) RETURN DATE -- ********************* -- ** f r o m _ 3 0 ** -- ********************* -- from_m30 returns the DATE corresponding to the m30 date passed -- in in_text ('YYYY-MM-DD' format). IS greg_year_val PLS_INTEGER; m30_day_val PLS_INTEGER := TO_NUMBER ( SUBSTR (in_text, 9, 2), '99' ); m30_month_val PLS_INTEGER := TO_NUMBER ( SUBSTR (in_text, 6, 2), '99' ); m30_new_year_dt DATE; m30_year_val PLS_INTEGER := TO_NUMBER ( SUBSTR (in_text, 1, 4), '9999' ); year_offset_val PLS_INTEGER := 78; -- Gregorian year of M30 year 0 BEGIN greg_year_val := m30_year_val + year_offset_val; RETURN TO_DATE ( TRIM ( TO_CHAR ( greg_year_val, '0000' ) ) || '0420', 'YYYYMMDD' ) + (30 * (m30_month_val - 1)) + m30_day_val; END from_m30; / CREATE OR REPLACE FUNCTION to_m30 ( in_date DATE ) RETURN CHAR -- ******************* -- ** t o _ m 3 0 ** -- ******************* -- to_m30 returns a string representing an M30 date in -- 'YYYY-MM-DD' format (the same format from_m30 uses). IS m30_day_val PLS_INTEGER; m30_new_year_date DATE; m30_month_val PLS_INTEGER := 1; m30_year_val PLS_INTEGER; year_begin_text CHAR (4); year_offset_val PLS_INTEGER := 78; -- Gregorian year of M30 year 0 BEGIN m30_year_val := TO_NUMBER ( TO_CHAR ( in_date, 'YYYY' ), '9999' ) - year_offset_val; IF TO_CHAR (in_date, 'MMDD') > '0420' THEN -- New year was in same Gregorian year year_begin_text := TO_CHAR ( in_date, 'YYYY' ); ELSE -- New Year was in previous Gregorian year year_begin_text := TO_CHAR ( ADD_MONTHS (in_date, -12), 'YYYY' ); m30_year_val := m30_year_val - 1; END IF; m30_new_year_date := TO_DATE ( year_begin_text || '-04-21', 'YYYY-MM-DD' ); m30_year_val := TO_NUMBER (year_begin_text, '9999') - year_offset_val; m30_month_val := 1; -- Assumed: may be changed in loop below m30_day_val := (in_date - m30_new_year_date) + 1; WHILE m30_day_val > 30 LOOP m30_day_val := m30_day_val - 30; m30_month_val := m30_month_val + 1; END LOOP; RETURN TRIM (TO_CHAR (m30_year_val, '0000')) || '-' || TRIM (TO_CHAR (m30_month_val, '00')) || '-' || TRIM (TO_CHAR (m30_day_val, '00')); END to_m30; /


This was first published in October 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close