Fiscal year search

[Twobridge]

Hi

I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this

Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00

I have tried

Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @year and 6/30/ + (@year +1)

Plus a variety of other fruitless concoctions....but nothing seems to
work. Any help would be appreciated.

[Twobridge]

Discovered answer on my own. Thanks anyway.
Twobridge wrote:

Hi

I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this

Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00

I have tried

Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @year and 6/30/ + (@year +1)

Plus a variety of other fruitless concoctions....but nothing seems to
work. Any help would be appreciated.

[Dan]

I've got a simlar problem - can you post your solution?
Dan

On Nov 29, 1:50 am, "Twobridge" <[e-mail]:

Discovered answer on my own. Thanks anyway.

Twobridge wrote:

Hi

I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this

Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00

I have tried

Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @year and 6/30/ + (@year +1)

Plus a variety of other fruitless concoctions....but nothing seems to
work. Any help would be appreciated.- Hide quoted text -- Show quoted text -

[Hugo Kornelis]

On 29 Nov 2006 01:28:49 -0800, Dan wrote:

>I've got a simlar problem - can you post your solution?
Dan

Hi Dan,

The best way to solve this is to have a calendar table (see
http://sqlserver2000.databases.aspfa...ar-table.html),
with FiscalYear as one of it's columns.

Second best is to build a date in string format, using a format that is
guaranteed to be unabiguous WRT the order of day and month: yyyymmdd.
For isntance, for a fiscal year that starts on july first:

DECLARE @FiscalYear int
SET @FiscalYear = 2006
SELECT something
FROM sometable
WHERE TheDate >= CAST(@Year AS varchar) + '0701'
AND TheDate < CAST(@Year + 1 AS varchar) + '0701'

You might want to read this as well:
http://www.karaszi.com/SQLServer/info_datetime.asp

--
Hugo Kornelis, SQL Server MVP

[Dan]

Hi Hugo

Thanks - i'll take a look

Dan
On Nov 30, 9:13 pm, Hugo Kornelis
<[e-mail]:

On 29 Nov 2006 01:28:49 -0800, Dan wrote:

I've got a simlar problem - can you post your solution?
DanHi Dan,

The best way to solve this is to have a calendar table (seehttp://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using...),
with FiscalYear as one of it's columns.

Second best is to build a date in string format, using a format that is
guaranteed to be unabiguous WRT the order of day and month: yyyymmdd.
For isntance, for a fiscal year that starts on july first:

DECLARE @FiscalYear int
SET @FiscalYear = 2006
SELECT something
FROM sometable
WHERE TheDate >= CAST(@Year AS varchar) + '0701'
AND TheDate < CAST(@Year + 1 AS varchar) + '0701'

You might want to read this as well:http://www.karaszi.com/SQLServer/info_datetime.asp

--
Hugo Kornelis, SQL Server MVP