SQLTeam.com | Weblogs | Forums

Finance Year date and CAST to Date

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 :grinning: