I have a query that grabs an employee's hours for the current fiscal year based on the GetDate(). But I realized that it will only work between May and December of the current year. Our fiscal year is May 1 - April 30. So if the GetDate() is September 10, 2014 then the StartDate and EndDate would be 5/1/2014 12:00:00 AM and 4/30/2015 12:00:00 AM respectively. But if GetDate() is February 15, 2015 (if it is run approximately 5 months from now) then YEAR(GETDATE()) will return 2015 and the StartDate would then be set at 5/1/2015 12:00:00 AM. I've tried modifying the code but I can't seem to get it to work. Since I don't want to wait until February 15th to test the code I tried putting a parameter or string with a date set after January 1st but I keep getting errors about the object type.
If anyone can help me figure out how to put a CASE in this code to test if GetDate() is > May 1 it would be greatly appreciated!!
SELECT WempID AS EmployeeID, WCltID AS ProjectID, WCltName AS ProjectName, Whours AS HoursWorked, CAST(RTRIM('May 1, ' + CAST(YEAR(GETDATE()) AS char))
AS DateTime) AS StartDate, CAST(RTRIM('April 30, ' + CAST(YEAR(GETDATE()) + 1 AS char)) AS DateTime) AS EndDate
WHERE (WempID = @CurrentUser) AND (Wdate BETWEEN CAST(RTRIM('May 1, ' + CAST(YEAR(GETDATE()) AS char)) AS DateTime) AND
CAST(RTRIM('April 30, ' + CAST(YEAR(GETDATE()) + 1 AS char)) AS DateTime))