# Age calculation in that Fiscal year

### 질문

• Hi Experts,

How do I calculate age calculation for that fiscal year, my fiscal year starts from July 01 to next year June 30th?

Example:

If I have the date '2012-07-01' then my fiscal year will be 2013 till to 2013-06-30

If its '2013-07-01' then my fiscal year will be 2014, till to 2014-06-30

Here I need to pull the records who has age between 20 years & 0 days and 20 years & 364 days in that fiscal year?

Thanks!

Red

2018년 7월 12일 목요일 오후 7:37

### 답변

```DECLARE @fiscalYear int;
SET @fiscalYear = 2012;
DECLARE @startDateOfFiscalYear date;
DECLARE @endDateOfFiscalYear date;

SET @startDateOfFiscalYear = CAST(YEAR(@fiscalYear) AS char(4)) + '-07-01';
SET @endDateOfFiscalYear = CAST(YEAR(@fiscalYear) + 1 AS char(4)) + '-06-30';

SELECT *
FROM YourTable
WHERE DATEADD(YEAR, 20, BirthDate) >= @startDateOfFiscalYear AND DATEADD(YEAR, 20, BirthDate) <= @endDateOfFiscalYear;```

A Fan of SSIS, SSRS and SSAS

2018년 7월 13일 금요일 오후 8:28
• To be precise I need pull all records who has age between 20 years & 0 days and 20 years & 364 days in time-period starting from '2012-07-01' to '2013-06-30'. The input will be the date of birth field of the person. Currently I'm using SQL Server 2008 and EOMONTH function doesn't work.

I tried with below modified query but it didnt work:

SELECT * FROM  Table
)

Thanks!

Red

But your date rang is not right, and the brackets are not in right place. Therefore, it should be like this.

```SELECT * FROM  Table
--)```

Since the version of your SQL Server is SQL Server 2008, please try this query.

```DECLARE @V_DATE DATE='2012-7-1'

SELECT [Field1 of person records],[Field2 of person records],[Field3 of person records],....
FROM Your_table

Best Regards,

Will

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

2018년 7월 16일 월요일 오전 9:17

### 모든 응답

• So what would be the user input? just a year?

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

2018년 7월 12일 목요일 오후 7:45
• Thanks for responding! The input will be the date of birth field and need to pull the person records who has age between 20 years & 0 days and 20 years & 364 days in that fiscal year.

Thanks!

Red

2018년 7월 12일 목요일 오후 7:58
• Declare @Date date='2012-7-02'
Select Case when Month(@Date) >=7 then Year(@Date)+1 else Year(@date) end as FiscalYear

2018년 7월 12일 목요일 오후 9:02
• Thanks for responding! The input will be the date of birth field and need to pull the person records who has age between 20 years & 0 days and 20 years & 364 days in that fiscal year.

Thanks!

Red

Hi Red,

You mean to want this?

```DECLARE @V_DATE DATE='2012-7-1'

SELECT [Field1 of person records],[Field2 of person records],[Field3 of person records],....
FROM Your_table
WHERE (DATEADD(YEAR,20,[date of birth field])>=@v_date AND  DATEADD(YEAR,20,[date of birth field])<=EOMONTH(@v_date,11))

Best Regards,

Will

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

2018년 7월 13일 금요일 오전 5:21
• To be precise I need pull all records who has age between 20 years & 0 days and 20 years & 364 days in time-period starting from '2012-07-01' to '2013-06-30'. The input will be the date of birth field of the person. Currently I'm using SQL Server 2008 and EOMONTH function doesn't work.

I tried with below modified query but it didnt work:

SELECT * FROM  Table
)

Thanks!

Red

2018년 7월 13일 금요일 오후 1:07
• ```DECLARE @dateOfToday date;
DECLARE @startDateOfFiscalYear date;
DECLARE @endDateOfFiscalYear date;

SET @dateOfToday = GETDATE();
SET @startDateOfFiscalYear = CASE WHEN MONTH(@dateOfToday) >= 7 THEN CAST(YEAR(@dateOfToday) AS char(4)) + '-07-01' ELSE CAST(YEAR(@dateOfToday) - 1 AS char(4)) + '-07-01' END;
SET @endDateOfFiscalYear = CASE WHEN MONTH(@dateOfToday) >= 7 THEN CAST(YEAR(@dateOfToday) + 1 AS char(4)) + '-06-30' ELSE CAST(YEAR(@dateOfToday) AS char(4)) + '-06-30' END;
--SELECT @dateOfToday, @startDateOfFiscalYear, @endDateOfFiscalYear;

SELECT *
FROM YourTable
WHERE DATEADD(YEAR, 20, BirthDate) >= @startDateOfFiscalYear AND DATEADD(YEAR, 20, BirthDate) <= @endDateOfFiscalYear;```

A Fan of SSIS, SSRS and SSAS

2018년 7월 13일 금요일 오후 1:27
• Thanks for your inputs. Some of the records were not pulled who has age between 20 years & 0 days and 20 years & 364 days in time-period starting from '2012-07-01' to '2013-06-30'. Only persons who have exact 20 years were pulled with this sql.

DECLARE @dateOfToday date;
DECLARE @startDateOfFiscalYear date;
DECLARE @endDateOfFiscalYear date;

SET @dateOfToday = GETDATE();
SET @startDateOfFiscalYear = CASE WHEN MONTH(@dateOfToday) >= 7 THEN CAST(YEAR(@dateOfToday) AS char(4)) + '-07-01' ELSE CAST(YEAR(@dateOfToday) - 1 AS char(4)) + '-07-01' END;
SET @endDateOfFiscalYear = CASE WHEN MONTH(@dateOfToday) >= 7 THEN CAST(YEAR(@dateOfToday) + 1 AS char(4)) + '-06-30' ELSE CAST(YEAR(@dateOfToday) AS char(4)) + '-06-30' END;
--SELECT @dateOfToday, @startDateOfFiscalYear, @endDateOfFiscalYear;

SELECT * FROM [dbo].[Field_ops_Children_Served]
WHERE DATEADD(YEAR, 20, birth_dtm) >= @startDateOfFiscalYear AND DATEADD(YEAR, 20, birth_dtm) <= @endDateOfFiscalYear;

Thanks!

Red

2018년 7월 13일 금요일 오후 6:15
• If you want to to check the ages from 2012, you have to change the value of @dateOfToday to 2012. Maybe the variable name @dateOfTodayis not good.

A Fan of SSIS, SSRS and SSAS

2018년 7월 13일 금요일 오후 8:24

```DECLARE @fiscalYear int;
SET @fiscalYear = 2012;
DECLARE @startDateOfFiscalYear date;
DECLARE @endDateOfFiscalYear date;

SET @startDateOfFiscalYear = CAST(YEAR(@fiscalYear) AS char(4)) + '-07-01';
SET @endDateOfFiscalYear = CAST(YEAR(@fiscalYear) + 1 AS char(4)) + '-06-30';

SELECT *
FROM YourTable
WHERE DATEADD(YEAR, 20, BirthDate) >= @startDateOfFiscalYear AND DATEADD(YEAR, 20, BirthDate) <= @endDateOfFiscalYear;```

A Fan of SSIS, SSRS and SSAS

2018년 7월 13일 금요일 오후 8:28
• To be precise I need pull all records who has age between 20 years & 0 days and 20 years & 364 days in time-period starting from '2012-07-01' to '2013-06-30'. The input will be the date of birth field of the person. Currently I'm using SQL Server 2008 and EOMONTH function doesn't work.

I tried with below modified query but it didnt work:

SELECT * FROM  Table
)

Thanks!

Red

But your date rang is not right, and the brackets are not in right place. Therefore, it should be like this.

```SELECT * FROM  Table
--)```

Since the version of your SQL Server is SQL Server 2008, please try this query.

```DECLARE @V_DATE DATE='2012-7-1'

SELECT [Field1 of person records],[Field2 of person records],[Field3 of person records],....
FROM Your_table