[RESOLVED] Select records from previous year starting on July 1st for each year

[dmacman1962]

Hi All,

I need to select all the records from the previous fiscal year (the fiscal starts on July 1st for each year).

I searched the site and haven't found any posts with this requirement. I also tried dev.mysql.com and could see how to write it to do that.

I need something like:

SELECT * FROM table WHERE SignupDate<'the current year' . '-07-01'

I appreciate the guidance,

Don

[toma42]

I had a fiscal period requirement for an employer once. The best and most appropriate course of action is to write a set of database functions to handle this.

For instance given an epoch of 8/8/99 and knowing each fiscal period is 91 days long (this is a floating fiscal period, not static 7/1/xx, the following function would return the fiscal year for the given date:

delimiter //
CREATE FUNCTION fiscal_year (d datetime) RETURNS integer
BEGIN
DECLARE year INTEGER;
DECLARE quarter INTEGER;
DECLARE diff INTEGER;
SET year = 2000;
SELECT (TO_DAYS(d) - TO_DAYS('1999-08-08')) into diff;
SET quarter = CEILING(diff/91) + (MOD(diff,91) = 0);
WHILE quarter > 4 DO
SET year = year + 1;
SET quarter = quarter - 4;
END WHILE;
RETURN year;
END;
//

You should create a simile for your fiscal needs.

In all I wrote the following fiscal functions:
fiscal_quarter
fiscal_year
fiscal_quarter_start
fiscal_quarter_end
fiscal_month
fiscal_month_start
fiscal_month_end
fiscal_month_number
fiscal_days_in_month
fiscal_month_day
fiscal_quarter_week
fiscal_quarter_day
fiscal_quarter_start
fiscal_quarter_end

That set of functions fulfilled every fiscal requirement.

[dmacman1962]

Thanks for the function, I will add it to my library.

I did solve this another way. I used:

SELECT
Id,
SignupDate
from table
WHERE SignupDate <=(DATE_SUB(NOW(), INTERVAL 1 YEAR))

And then I run it as a scheduled task on that date.

Thanks for the help.

Don