SELECTĝATE '' AS a_dateğROM dual UNION ALLĪLTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY Day' Here's some code I wrote to test the function: You could also include every week in that table. If the PL/SQL function proves to be too slow, you could use it to populate a table that contained the fiscal year starting points for all years in which you might be interested, and join to that table. You could do the whole job in pure SQL, but it would be messy, and you'd have to copy that messy calculation in every query that used the fiscal year, which I imagine will be a lot of places. You may want to write another function that does that forumula )which finds how may days have passed since the beginning of the fiscal year, dvides by 7 to get the number of completed weeks (0-52), and adds 1 to get the number of the week in progress. To find the week number of a given date a_date, you can use this formula:ġ + FLOOR ( (a_date - fiscal_year_begin (a_date)) These days could be in the following fiscal year, so we check when that fiscal year begins and, if that date is less than in_date, the later date will be the one returned. The only exceptions are the days between January 26 amd January 31, inclusive. In most cases, this will be the beginning of the fiscal year. Next_fiscal_year_begin := fiscal_year_begin (in_date + 7) įirst, it finds the Saturday on or before February 1 in the same calendar year as in_date (or, if in_date is in January, the previous calendar year). Return_date := NEXT_DAY ( ADD_MONTHS ( TRUNC ( ADD_MONTHS (in_date, -1) Here's a PL/SQL function that returns the beginning of the fiscal year which contains a given date, in_date:ĬREATE OR REPLACE FUNCTION fiscal_year_begin 1.7K Training / Learning / Certification.165.3K Java EE (Java Enterprise Edition).7.9K Oracle Database Express Edition (XE).3.8K Java and JavaScript in the Database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |