I've a following SQL like to replace the hard code values '2019' using '01042019' and '31032020'
where tblVTest.DateConfirmed >= '20190401' and tblVTest.DateConfirmed <= '20200331' )
I would like to replace above values '20190401' and '201200331' using current FY start and end date something like this:
SELECT QM_FIN_YEAR =
CASE WHEN Month(GETDATE()) BETWEEN 4 AND 12 THEN
CAST('0104'+CONVERT(VARCHAR(4),YEAR(GETDATE())) AS varchar)
--WHEN Month(GETDATE()) BETWEEN 1 AND 3 -- THEN CONVERT(VARCHAR(4),YEAR(GETDATE()) - 1) + '-' + CONVERT(VARCHAR(4),YEAR(GETDATE()) )
End ORDER by 1 DESC
Appreciate you help
Thanks
Farhan
from dbo.table_name
cross apply (
SELECT QM_FIN_YEAR = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) / 12 * 12 +
3 + CASE WHEN MONTH(GETDATE()) < 4 THEN -12 ELSE 0 END, 0)
) as calc_finance_year_start
where tblVTest.DateConfirmed >= QM_FIN_YEAR and
tblVTest.DateConfirmed < DATEADD(YEAR, 1, QM_FIN_YEAR)
Thanks a lot Scott it works for me 
Hi Again
I also would like to handle the previous/current year.
The day(dd) and month(mm) would be constant values like
"From: 01/10/yy and To: 31/05/yy" and the year value will be change based on previous/current year
for example in the following SQL should be like:
From: 01/10/ PrvYear (2018) and To : 31/05/ CurrentYear (2019)
CAST(ServiceEnd as DATE) between '20181001' and '20190531'
Modify the Scott sql by replacing '3' to '9' i thought it work but doesn't pleas advise. thanks
) FROM table
CROSS APPLY(
SELECT QM_FIN_YEAR = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) / 12 * 12 +
9 + CASE WHEN MONTH(GETDATE()) < 4 THEN -12 ELSE 0 END, 0)
) as calc_finance_year_start
where tblVisit.DateServiceEnd >= QM_FIN_YEAR and tblVisit.DateServiceEnd < DATEADD(YEAR, 1, QM_FIN_YEAR)
AND tblContractSite.CardNumber =
tblVisit.CardNumber
