Solved

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

Posted on 2012-03-16
6
704 Views
Last Modified: 2012-03-16
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
Comment
Question by:HSI_guelph
  • 3
  • 3
6 Comments
 
LVL 18

Accepted Solution

by:
Cluskitt earned 500 total points
ID: 37730262
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
 

Author Comment

by:HSI_guelph
ID: 37730313
It's saying Conversion failed when converting character string to smalldatetime data type.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37730331
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Closing Comment

by:HSI_guelph
ID: 37730335
Oh there was just a / missing from the 04/30 part!  Its working now, tyvm!!!!!!!
0
 

Author Comment

by:HSI_guelph
ID: 37730397
Its working great, thank you!!!!
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37730401
Glad to help.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 66
Slow Connectivity over ODBC 8 32
How to share SSIS Package? 6 36
optimize stored procedure 6 23
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only seeā€¦
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question