Detrermining whether invoice date is within current financial year

[thebigcheese]

I have a sales transaction database (SQL Server 2K) with invoices all having an invoice date. Our Financial Year runs 1 October YYYY thru 30 September YYYY+1.

I have the following code for Oracle DB, but seek assistance in modifying this for SQL Server.

Code is:

TABLE_NAME.INVOICE_DATE >=  to_Date('01/07/' || DECODE (sign(7-to_char(sysdate, 'MM')),1,to_char(sysdate, 'YYYY') - 1,to_char(sysdate, 'YYYY')),'DD/MM/YYYY')   and TABLE_NAME.INVOICE_DATE <= sysdate
 
Previous Financial Year    This assumes a Financial Year of 1/7 to 30/6
 
TABLE_NAME.INVOICE_DATE between  to_Date('01/07/' || DECODE (sign(7-to_char(sysdate, 'MM')),1,to_char(sysdate, 'YYYY') - 2,to_char(sysdate, 'YYYY')-1),'DD/MM/YYYY')   and  to_Date('30/06/' || DECODE (sign(7-to_char(sysdate, 'MM')),1,to_char(sysdate, 'YYYY') - 1,to_char(sysdate, 'YYYY')),'DD/MM/YYYY')

Please Help !!

 

[Durkin]

I haven't worked out exactly the dates you need but something like this should do it.

SELECT CONVERT(DATETIME, '01-Oct-' +
	 CASE 
		WHEN MONTH(GETDATE()) < 10 
			THEN CONVERT(VARCHAR(4),YEAR(GETDATE()) - 1)
		ELSE CONVERT(VARCHAR(4),YEAR(GETDATE()))
		END)

Durkin