?
Solved

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

Posted on 2012-03-16
6
Medium Priority
?
716 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 18

Accepted Solution

by:
Cluskitt earned 2000 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
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!

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

765 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