[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Date falls within Fiscal Year Query

Posted on 2006-07-11
15
Medium Priority
?
791 Views
Last Modified: 2008-01-09
i have a date field  (DATE datatype in Oracle 9i) in a table along with other fields. I need to have an sql query which filters out rows that are only in the current fiscal year.

The sql query reads like this:

select other_field from table where date_field falls within current fiscal year
0
Comment
Question by:lcor
[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
  • 5
  • 3
  • 3
  • +3
15 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17083856
No problem.  Please define 'fiscal year'
0
 

Author Comment

by:lcor
ID: 17083861
The solution doesn't have to be portable so it can be Oracle specific
0
 

Author Comment

by:lcor
ID: 17083866
fiscal year is Oct - Sept
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 17083869
Jim,

Ton of FY questions this week.  Must be lots of companies with July-June FY closing the books :)

Regards,

Patrick
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17083892
Yep...

The Quick & Dirty way would be to embed the dates in your query...

select other_field from table where date_field BETWEEN '10/1/2005' AND '9/30/2006'

The better way (which most developers use) is to have a table of days (tbl_days, tbl_time, or tbl_months for just month if that floats your boat) where you have a column that determines fiscal year.  Then you can use queries that go like this...

select other_field from table
INNER JOIN tbl_time ON table.datefield = time.datefield
where tbl_time.FY = '2005'
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17083906
re: tbl_time, in mine I also have columns for fiscal month, fiscal week, fiscal quarter, calendar month, calendar week, holiday (yes/no), which can be real darn handy.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17084184
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 2000 total points
ID: 17084312

Try:

select * from the_table
where to_char(add_months(trunc(the_date),+3),'YYYY') = to_char(trunc(sysdate),'yyyy')

Gives current FY.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 17084334

PS: If sysdate within FY, otherwise, hard-code the FY:

select * from the_table
where to_char(add_months(trunc(the_date),+3),'YYYY') = '2006'

0
 
LVL 1

Expert Comment

by:davetheravesmith
ID: 17085246
Chaps,

My fiscal year is April to March, so I just use logic like this...

if month(date) < 4 then year(date) -1 else year (date)

What this does is that if the month of the date fall in Jan Feb or Mar, it's Fyear is one less than the real year, otherwise it is the same.

Very simple logic / very simply fomula

Regards

DAVE S
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 17085351

Depends on the particular definition of fiscal year, for example:

If fiscal year is Oct'05 - Sept'06, then you have to ADD 3 months to the date to get the actual fiscal year (which would be: 2006).

Example:

to_char(add_months(trunc(the_date),+3),'YYYY')





 
0
 
LVL 1

Expert Comment

by:davetheravesmith
ID: 17089423
In my case the fiscal year takes the year of the start month, hence Jan Feb & Mar this year (2006) belong to fiscal year 2005.

Just a question, why all the to-char business?  Wouldn't it be sufficient to just display the numeric returned by the YEAR function?

Regards

DAVE S
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 17090624

Then you do need to subtract 9 from the date:

to_char(add_months(trunc(the_date),-9),'YYYY')

the to_char is to convert the DATE to CHAR depending on th eformat supplied YYYY.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 17090650

Are you sure of the fiscal year designation?

Normally fiscal years beginning in the first semester are named after the year of beginning month and fy beginning in second semester are normally named after the ending month's year.

But who know's? Every other company may follow it's own rules.
0
 

Author Comment

by:lcor
ID: 17141039
MikeOM_DBA's is the best solution..works everytime...thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

656 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