Solved

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

Posted on 2012-03-16
6
706 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

733 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