• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 734
  • Last Modified:

Assigning a BETWEEN date range for fiscal year based on today's date

I'm trying to grab employee hours for the current fiscal year.  What I want to do is take today's date, since the user will be interested in the current fiscal year, and if its before May 1st then the pay period BETWEEN would be May 1, 2011 - April 30, 2012.  If the pay period is on or after May 1st then the pay period BETWEEN would be May 1, 2012 - April 30, 2013.  I can assume that the end date of the range is the date that the report is being run (since no hours would be posted for a future period that hasn't happened yet).  But how do I work in a condition in the middle of the main select statement?  I don't want to create a table for this, I want it to work dynamically.

example I have working
SELECT     Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, SUM(WIP.Whours) AS Hours
FROM         WIP INNER JOIN
                      Employee ON WIP.WempID = Employee.ID INNER JOIN
WHERE    (WIP.Wdate BETWEEN
                      '05/01/' + '2011' + ' 12:00:00 AM' AND GETDATE())

I thought that I could swap out the 2011 with a conditional statement that would take the current year from today's date and + or - a year depending on where in the year it falls but I'm getting errors regarding formatting small date.  

Can I replace the whole '05/01/' + '2011' + ' 12:00:00 AM' with a conditional select or can I create a variable that does the conditional testing before it gets to that part in the code?
0
HSI_guelph
Asked:
HSI_guelph
  • 3
  • 3
1 Solution
 
CluskittCommented:
WHERE    WIP.Wdate BETWEEN '05/01/' + CASE WHEN MONTH(GETDATE())<5 THEN CAST(YEAR(GETDATE())-1 AS char(4)) ELSE CAST(YEAR(GETDATE()) AS char(4)) END + ' 12:00:00 AM' AND '04/30' + CASE WHEN MONTH(GETDATE())<5 THEN CAST(YEAR(GETDATE()) AS char(4)) ELSE CAST(YEAR(GETDATE())+1 AS char(4)) END + ' 11:59:00 PM'
0
 
HSI_guelphAuthor Commented:
It's saying Conversion failed when converting character string to smalldatetime data type.
0
 
CluskittCommented:
Probably because I made a typo: '04/30/' not '04/30'
What I did was create the field '05/01/2011 12:00:00 AM'. Then I replaced the year with a case statement. If current date is before may, then last year, else, this year. Then the same for the other one, except I add one year to that.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HSI_guelphAuthor Commented:
Oh there was just a / missing from the 04/30 part!  Its working now, tyvm!!!!!!!
0
 
HSI_guelphAuthor Commented:
Its working great, thank you!!!!
0
 
CluskittCommented:
Glad to help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now