I have a query that I am using to pull hours for specific employees based on Fiscal Year. The month and day of the Fiscal year is static but the year of course changes.
This year the Fiscal year runs from 07/01/2010 to 06/30/2011. (2011 Fiscal Year)
There is one parameter for the Fiscal Year: @FiscYear
I would like to let the user enter the Fiscal Year and have it pull the data between the two dates above, if they enter 2011. If they would enter 2012 the dates would dynmically become 07/01/2011 to 06/30/2012.
I know this syntax is incorrect, but I think you can get my idea from it:
SELECT WempID, Windicator, Whours, Wdate, CASE WIP.Wbillable WHEN '1' THEN WIP.Whours ELSE 0 END AS 'BillActual'
WHERE (Wdate BETWEEN convert(datetime,'07/01/' + (@FiscYear -1),101) AND convert(datetime, '06/30/' + @FiscYear,101)) AND
(WempID = @MyID) AND (Windicator <> 'D')
Error Recieved: Conversion failed when converting the varchar value '07/01/' to data type int.