ITMcmcpa
asked on
SQL - Between Dates with Dynamic Year from Parameter
I have a query that I am using to pull hours for specific employees based on Fiscal Year. The month and day of the Fiscal year is static but the year of course changes.
This year the Fiscal year runs from 07/01/2010 to 06/30/2011. (2011 Fiscal Year)
There is one parameter for the Fiscal Year: @FiscYear
I would like to let the user enter the Fiscal Year and have it pull the data between the two dates above, if they enter 2011. If they would enter 2012 the dates would dynmically become 07/01/2011 to 06/30/2012.
I know this syntax is incorrect, but I think you can get my idea from it:
Error Recieved: Conversion failed when converting the varchar value '07/01/' to data type int.
Thanks!
This year the Fiscal year runs from 07/01/2010 to 06/30/2011. (2011 Fiscal Year)
There is one parameter for the Fiscal Year: @FiscYear
I would like to let the user enter the Fiscal Year and have it pull the data between the two dates above, if they enter 2011. If they would enter 2012 the dates would dynmically become 07/01/2011 to 06/30/2012.
I know this syntax is incorrect, but I think you can get my idea from it:
SELECT WempID, Windicator, Whours, Wdate, CASE WIP.Wbillable WHEN '1' THEN WIP.Whours ELSE 0 END AS 'BillActual'
FROM WIP
WHERE (Wdate BETWEEN convert(datetime,'07/01/' + (@FiscYear -1),101) AND convert(datetime, '06/30/' + @FiscYear,101)) AND
(WempID = @MyID) AND (Windicator <> 'D')
Error Recieved: Conversion failed when converting the varchar value '07/01/' to data type int.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works perfect. Thank you very much!
For performance considerations as well do not use cast/convert or other functions in WHERE clause as they invalidate existing indexes and make sure you match the table.column datatype
declare @FiscYear sysname
set @FiscYear = 2012
IF @FiscYear = (select(year(getdate()))) set @FiscYear = 2010;
IF @FiscYear = (select(year(getdate()))+1 ) set @FiscYear = 2011;
print @FiscYear
declare @startdate datetime
declare @enddate sysname
set @startdate = (select convert(datetime,'07/01/' + @FiscYear,101))
set @enddate = (select convert(datetime, '06/30/' + @FiscYear,101))
SELECT WempID, Windicator, Whours, Wdate, CASE WIP.Wbillable WHEN '1' THEN WIP.Whours ELSE 0 END AS 'BillActual'
FROM WIP
WHERE Wdate BETWEEN @startdate AND @enddate AND WempID = @Myid AND Windicator <> 'D'
declare @FiscYear sysname
set @FiscYear = 2012
IF @FiscYear = (select(year(getdate()))) set @FiscYear = 2010;
IF @FiscYear = (select(year(getdate()))+1
print @FiscYear
declare @startdate datetime
declare @enddate sysname
set @startdate = (select convert(datetime,'07/01/' + @FiscYear,101))
set @enddate = (select convert(datetime, '06/30/' + @FiscYear,101))
SELECT WempID, Windicator, Whours, Wdate, CASE WIP.Wbillable WHEN '1' THEN WIP.Whours ELSE 0 END AS 'BillActual'
FROM WIP
WHERE Wdate BETWEEN @startdate AND @enddate AND WempID = @Myid AND Windicator <> 'D'
ASKER
roma1123 yours worked too. Sorry I didn't see your post at first for some reason.
:)
No worries
No worries
FROM WIP
WHERE (Wdate BETWEEN convert(datetime,'07/01/' + CONVERT(VARCHAR,(@FiscYear
(WempID = @MyID) AND (Windicator <> 'D')