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:

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

hi

this may help !!!! :slight_smile: :slight_smile: