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

HSI_guelphAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
CAST(RTRIM('May 1, ' + CAST(YEAR(GETDATE()) + CASE WHEN MONTH(GETDATE()) < 5 THEN -1 ELSE 0 END AS char))  AS DATETIME)

and

CAST(RTRIM('April 30, ' + CAST(YEAR(GETDATE()) + CASE WHEN MONTH(GETDATE()) < 5 THEN 0 ELSE 1 END AS char)) AS DATETIME)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Do NOT use BETWEEN for this.

Do it like this:
FROM         WIP
WHERE    ...
AND Wdate >= '20130501'
AND Wdate  < '20140501'

And, please oh please, do NOT rely on language when dealing with dates (e.g. "May" is English) .

For versions of SQL Server without datefromparts():
select
    convert(varchar(23),Wdate,121) as Wdate
from WIP
where Wdate >= case when month(getdate()) < 5 then 
                    cast(convert(varchar(4),year(getdate()) - 1) + '0501' as datetime)
                else 
                    cast(convert(varchar(4),year(getdate())) + '0501' as datetime)
                end
and   Wdate <  case when month(getdate()) < 5 then 
                    dateadd(year,1,cast(convert(varchar(4),year(getdate()) - 1) + '0501' as datetime))
                else 
                    dateadd(year,1,cast(convert(varchar(4),year(getdate())) + '0501' as datetime))
                end
order by Wdate
;

Open in new window


For versions of SQL Server with datefromparts():
select
    convert(varchar(23),Wdate,121) as Wdate
from WIP
where Wdate >= case when month(getdate()) < 5 then 
                    datefromparts(year(getdate()) - 1 ,5,1)
                else 
                    datefromparts(year(getdate()) ,5,1)
                end
and   Wdate <  case when month(getdate()) < 5 then 
                    datefromparts(year(getdate()) ,5,1)
                else 
                    datefromparts(year(getdate()) + 1 ,5,1)
                end
order by Wdate
;

Open in new window


I have setup a demonstration for you, HERE, please take particular note of the 30th of April dates and TIME

for more about between please see: "Beware of Between"

Setup details of that demonstration:
    
    
    CREATE TABLE WIP
    	([Wdate] datetime)
    ;
    	
    INSERT INTO WIP
    	([Wdate])
    VALUES
    	('2012-12-31 00:00:00'),
    	('2013-01-01 00:00:00'),
    	('2013-01-31 00:00:00'),
    	('2013-02-01 00:00:00'),
    	('2013-02-28 00:00:00'),
    	('2013-03-01 00:00:00'),
    	('2013-03-31 00:00:00'),
    	('2013-04-01 00:00:00'),
    	('2013-04-30 00:00:00'),
    	('2013-05-01 00:00:00'),
    	('2013-05-31 00:00:00'),
    	('2013-06-01 00:00:00'),
    	('2013-06-30 00:00:00'),
    	('2013-07-01 00:00:00'),
    	('2013-07-31 00:00:00'),
    	('2013-08-01 00:00:00'),
    	('2013-08-31 00:00:00'),
    	('2013-09-01 00:00:00'),
    	('2013-09-30 00:00:00'),
    	('2013-10-01 00:00:00'),
    	('2013-10-31 00:00:00'),
    	('2013-11-01 00:00:00'),
    	('2013-11-30 00:00:00'),
    	('2013-12-01 00:00:00'),
    	('2013-12-31 00:00:00'),
    	('2014-01-01 00:00:00'),
    	('2014-01-31 00:00:00'),
    	('2014-02-01 00:00:00'),
    	('2014-02-28 00:00:00'),
    	('2014-03-01 00:00:00'),
    	('2014-03-31 00:00:00'),
    	('2014-04-01 00:00:00'),
    	('2014-04-30 00:00:00'),
    	('2014-05-01 00:00:00'),
    	('2014-05-31 00:00:00'),
    	('2014-06-01 00:00:00'),
    	('2014-06-30 00:00:00'),
    	('2014-07-01 00:00:00'),
    	('2014-07-31 00:00:00'),
    	('2014-08-01 00:00:00'),
    	('2014-08-31 00:00:00'),
    	('2014-09-01 00:00:00'),
    	('2014-09-30 00:00:00'),
    	('2014-10-01 00:00:00'),
    	('2014-10-31 00:00:00'),
    	('2014-11-01 00:00:00'),
    	('2014-11-30 00:00:00'),
    	('2014-12-01 00:00:00'),
    	('2014-12-31 00:00:00'),
    	('2015-01-01 00:00:00'),
    	('2015-01-31 00:00:00'),
    	('2015-02-01 00:00:00'),
    	('2015-02-28 00:00:00'),
    	('2015-03-01 00:00:00'),
    	('2015-03-31 00:00:00'),
    	('2015-04-01 00:00:00'),
    	('2015-04-30 00:00:00'),
    	('2015-05-01 00:00:00'),
    	('2015-05-31 00:00:00'),
    	('2015-06-01 00:00:00'),
    	('2015-06-30 00:00:00'),
    	('2015-07-01 00:00:00'),
    	('2015-07-31 00:00:00'),
    	('2015-08-01 00:00:00'),
    	('2015-08-31 00:00:00'),
    	('2015-09-01 00:00:00'),
    	('2015-09-30 00:00:00'),
    	('2015-10-01 00:00:00'),
    	('2015-10-31 00:00:00'),
    	('2015-11-01 00:00:00'),
    	('2015-11-30 00:00:00'),
    	('2015-12-01 00:00:00'),
    	('2015-12-31 00:00:00'),
    	('2016-01-01 00:00:00')
    ;

**Query 1**:

    update WIP
    set Wdate = dateadd(millisecond,-4,dateadd(day,1,Wdate))
    where day(Wdate) <> 1
    

**[Results][2]**:
    

**Query 2**:

    select
        convert(varchar(23),Wdate,121) as Wdate
    from WIP
    where Wdate >= case when month(getdate()) < 5 then 
                        cast(convert(varchar(4),year(getdate()) - 1) + '0501' as datetime)
                    else 
                        cast(convert(varchar(4),year(getdate())) + '0501' as datetime)
                    end
    and   Wdate <  case when month(getdate()) < 5 then 
                        dateadd(year,1,cast(convert(varchar(4),year(getdate()) - 1) + '0501' as datetime))
                    else 
                        dateadd(year,1,cast(convert(varchar(4),year(getdate())) + '0501' as datetime))
                    end
    order by Wdate
    

**[Results][3]**:
    
    |                   WDATE |
    |-------------------------|
    | 2014-05-01 00:00:00.000 |
    | 2014-05-31 23:59:59.997 |
    | 2014-06-01 00:00:00.000 |
    | 2014-06-30 23:59:59.997 |
    | 2014-07-01 00:00:00.000 |
    | 2014-07-31 23:59:59.997 |
    | 2014-08-01 00:00:00.000 |
    | 2014-08-31 23:59:59.997 |
    | 2014-09-01 00:00:00.000 |
    | 2014-09-30 23:59:59.997 |
    | 2014-10-01 00:00:00.000 |
    | 2014-10-31 23:59:59.997 |
    | 2014-11-01 00:00:00.000 |
    | 2014-11-30 23:59:59.997 |
    | 2014-12-01 00:00:00.000 |
    | 2014-12-31 23:59:59.997 |
    | 2015-01-01 00:00:00.000 |
    | 2015-01-31 23:59:59.997 |
    | 2015-02-01 00:00:00.000 |
    | 2015-02-28 23:59:59.997 |
    | 2015-03-01 00:00:00.000 |
    | 2015-03-31 23:59:59.997 |
    | 2015-04-01 00:00:00.000 |
    | 2015-04-30 23:59:59.997 |


**Query 3**:

    select
        convert(varchar(23),Wdate,121) as Wdate
    from WIP
    where Wdate >= case when month(getdate()) < 5 then 
                        datefromparts(year(getdate()) - 1 ,5,1)
                    else 
                        datefromparts(year(getdate()) ,5,1)
                    end
    and   Wdate <  case when month(getdate()) < 5 then 
                        datefromparts(year(getdate()) ,5,1)
                    else 
                        datefromparts(year(getdate()) + 1 ,5,1)
                    end
    order by Wdate
    

**[Results][4]**:
    
    |                   WDATE |
    |-------------------------|
    | 2014-05-01 00:00:00.000 |
    | 2014-05-31 23:59:59.997 |
    | 2014-06-01 00:00:00.000 |
    | 2014-06-30 23:59:59.997 |
    | 2014-07-01 00:00:00.000 |
    | 2014-07-31 23:59:59.997 |
    | 2014-08-01 00:00:00.000 |
    | 2014-08-31 23:59:59.997 |
    | 2014-09-01 00:00:00.000 |
    | 2014-09-30 23:59:59.997 |
    | 2014-10-01 00:00:00.000 |
    | 2014-10-31 23:59:59.997 |
    | 2014-11-01 00:00:00.000 |
    | 2014-11-30 23:59:59.997 |
    | 2014-12-01 00:00:00.000 |
    | 2014-12-31 23:59:59.997 |
    | 2015-01-01 00:00:00.000 |
    | 2015-01-31 23:59:59.997 |
    | 2015-02-01 00:00:00.000 |
    | 2015-02-28 23:59:59.997 |
    | 2015-03-01 00:00:00.000 |
    | 2015-03-31 23:59:59.997 |
    | 2015-04-01 00:00:00.000 |
    | 2015-04-30 23:59:59.997 |


**Query 4**:

    select
        FY
      , FYStart
      , convert(varchar(23),Wdate,121)
      , dateadd(year,1,FYStart) as FYEnd
    from WIP
    cross apply (
    
                select
                    case when month(Wdate) < 5 then 
                        year(Wdate) - 1
                    else 
                        year(Wdate)
                    end as FY
                
                  , case when month(Wdate) < 5 then 
                        cast(convert(varchar(4),year(Wdate) - 1) + '0501' as date)
                    else 
                        cast(convert(varchar(4),year(Wdate)) + '0501' as date)
                    end as FYstart  
                  
                   ) ca (FY, FYStart)
    

**[Results][5]**:
    
    |   FY |    FYSTART |                COLUMN_2 |      FYEND |
    |------|------------|-------------------------|------------|
    | 2012 | 2012-05-01 | 2012-12-31 23:59:59.997 | 2013-05-01 |
    | 2012 | 2012-05-01 | 2013-01-01 00:00:00.000 | 2013-05-01 |
    | 2012 | 2012-05-01 | 2013-01-31 23:59:59.997 | 2013-05-01 |
    | 2012 | 2012-05-01 | 2013-02-01 00:00:00.000 | 2013-05-01 |
    | 2012 | 2012-05-01 | 2013-02-28 23:59:59.997 | 2013-05-01 |
    | 2012 | 2012-05-01 | 2013-03-01 00:00:00.000 | 2013-05-01 |
    | 2012 | 2012-05-01 | 2013-03-31 23:59:59.997 | 2013-05-01 |
    | 2012 | 2012-05-01 | 2013-04-01 00:00:00.000 | 2013-05-01 |
    | 2012 | 2012-05-01 | 2013-04-30 23:59:59.997 | 2013-05-01 |
    | 2013 | 2013-05-01 | 2013-05-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-05-31 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-06-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-06-30 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-07-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-07-31 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-08-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-08-31 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-09-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-09-30 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-10-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-10-31 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-11-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-11-30 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-12-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2013-12-31 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2014-01-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2014-01-31 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2014-02-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2014-02-28 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2014-03-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2014-03-31 23:59:59.997 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2014-04-01 00:00:00.000 | 2014-05-01 |
    | 2013 | 2013-05-01 | 2014-04-30 23:59:59.997 | 2014-05-01 |
    | 2014 | 2014-05-01 | 2014-05-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-05-31 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-06-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-06-30 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-07-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-07-31 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-08-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-08-31 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-09-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-09-30 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-10-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-10-31 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-11-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-11-30 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-12-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2014-12-31 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2015-01-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2015-01-31 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2015-02-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2015-02-28 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2015-03-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2015-03-31 23:59:59.997 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2015-04-01 00:00:00.000 | 2015-05-01 |
    | 2014 | 2014-05-01 | 2015-04-30 23:59:59.997 | 2015-05-01 |
    | 2015 | 2015-05-01 | 2015-05-01 00:00:00.000 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-05-31 23:59:59.997 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-06-01 00:00:00.000 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-06-30 23:59:59.997 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-07-01 00:00:00.000 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-07-31 23:59:59.997 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-08-01 00:00:00.000 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-08-31 23:59:59.997 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-09-01 00:00:00.000 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-09-30 23:59:59.997 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-10-01 00:00:00.000 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-10-31 23:59:59.997 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-11-01 00:00:00.000 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-11-30 23:59:59.997 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-12-01 00:00:00.000 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2015-12-31 23:59:59.997 | 2016-05-01 |
    | 2015 | 2015-05-01 | 2016-01-01 00:00:00.000 | 2016-05-01 |



  [1]: http://sqlfiddle.com/#!6/6b37d/1

  [2]: http://sqlfiddle.com/#!6/6b37d/1/0

  [3]: http://sqlfiddle.com/#!6/6b37d/1/1

  [4]: http://sqlfiddle.com/#!6/6b37d/1/2

  [5]: http://sqlfiddle.com/#!6/6b37d/1/3

Open in new window

date format styles: SQL Server Date Styles (formats) using CONVERT()
0
HSI_guelphAuthor Commented:
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!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HSI_guelphAuthor Commented:
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

0
PortletPaulfreelancerCommented:
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)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.