Fiscal year search

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.Discovered answer on my own. Thanks anyway.

Twobridge wrote:

Quote:

Originally Posted by

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.

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

On Nov 29, 1:50 am, "Twobridge" <Twobri...@.gmail.comwrote:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

Hi

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

I have tried

>

Quote:

Originally Posted by

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)

>

Quote:

Originally Posted by

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

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

Quote:

Originally Posted by

>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.aspf...dar-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|||Hi Hugo

Thanks - i'll take a look

Dan
On Nov 30, 9:13 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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