asked on

Set Fiscal start and end dates based on today's date

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
FROM         WIP
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))

Open in new window

ASKER CERTIFIED SOLUTION

Shaun Kline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HSI_guelph

ASKER

Thank you both for your help! Shaun Kline's solution worked and was first but I had no idea about the BETWEEN issue until PortletPaul posted it. So you both helped build my query. Thank you!

HSI_guelph

ASKER

Final Code

SELECT     WempID AS EmployeeID, WCltID AS ProjectID, WCltName AS ProjectName, Whours AS HoursWorked, CAST(RTRIM('May 1, ' + CAST(YEAR(GETDATE()) 
                      + CASE WHEN MONTH(GetDate()) < 5 THEN - 1 ELSE 0 END AS char)) AS DATETIME) AS StartDate, CAST(RTRIM('April 30, ' + CAST(YEAR(GETDATE()) 
                      + CASE WHEN MONTH(GetDate()) < 5 THEN 0 ELSE 1 END AS char)) AS DATETIME) AS EndDate
FROM         WIP
WHERE     (WempID = @CurrentUser) AND (Wdate >= CAST(RTRIM('May 1, ' + CAST(YEAR(GETDATE()) + CASE WHEN MONTH(GetDate()) < 5 THEN - 1 ELSE 0 END AS char)) 
                      AS DATETIME)) AND (Wdate < CAST(RTRIM('April 30, ' + CAST(YEAR(GETDATE()) + CASE WHEN MONTH(GetDate()) < 5 THEN 0 ELSE 1 END AS char)) AS DATETIME))

Open in new window

PortletPaul

That final query is wrong!

now you have excluded April 30th completely

You need to use "May 1" for BOTH the start and the end,

But I do mean this most sincerely, you should not use language based strings for this.

Try reading this:
The ultimate guide to the datetime datatypes

and this:
"Bad habits to kick : mis-handling date / range queries"

The safest possible date literal format in SQL Server is 'YYYYMMDD'

and it isn't a big ask for you to stop using "May 1" and use "0501" instead.

---
I am very pleased you learned about the dangers of between, but you must also remember to move up the higher date
(i.e. where you once used April 30 you add 1 day and now use May 1, but as numbers not month names)

Jim Horn

HSI - I just kicked out an article that deals specifically with Fiscal calendar planning --> SQL Server Calendar Table: Fiscal Years. Let me know if this helps you, and if yes please click on the 'Good Article' button and provide some feedback. Thanks.