471,066 Members | 2,150 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

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.

Nov 29 '06 #1
4 2537
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.
Nov 29 '06 #2
Dan
I've got a simlar problem - can you post your solution?
Dan

On Nov 29, 1:50 am, "Twobridge" <Twobri...@gmail.comwrote:
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 -
Nov 29 '06 #3
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
Nov 30 '06 #4
Dan

Hi Hugo

Thanks - i'll take a look

Dan
On Nov 30, 9:13 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALIDwrote:
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
Dec 2 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by MissiMaths | last post: by
2 posts views Thread by JohnC | last post: by
2 posts views Thread by Sund via AccessMonster.com | last post: by
4 posts views Thread by ltazz | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.