Solved

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

Posted on 2012-03-16
6
705 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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