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 last published in October 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close