SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Fiscal Year date fuction
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MxWebb
Starting Member

USA
8 Posts

Posted - 05/20/2010 :  10:39:07  Show Profile  Reply with Quote
I need a couple of SQL User functions.

What I am trying do is use function that I can send GetDate() and it will return me StartDate of the fiscal year. (Example 2009 StartDate = '9/30/2008')

Same for EndDate (Example 2009 EndDate = '10/01/2009 )


I will use them in a where clause to return date for a date range.



Thanks
Michael Webb

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 05/20/2010 :  11:41:49  Show Profile  Reply with Quote
I'd hold out for a better answer, which will come from someone else shortly, but this gives you something to start with.
Jim

declare @basedate datetime
declare @date datetime
declare @startdate datetime
declare @enddate datetime

set @date = '20111201'

SET @startdate =
                 case 
                      when datepart(q,@date) < 4 then dateadd(q,datediff(q,'19000930',@date),0)
                      else  dateadd(q,datediff(q,0,@date),0)
                 end

set @enddate = dateadd(year,1,@startdate)  - 1
select @startdate,@enddate


Everyday I learn something that somebody else already knew
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/20/2010 :  11:54:10  Show Profile  Reply with Quote
Your fiscal year stars on the last day of September and ends on the first day of October?

Just asking, because it would seem more likely for the fiscal year to start on the first day of October and end on last day of September of the following calendar year. For example: 2008-10-01 through 2009-09-30




CODO ERGO SUM

Edited by - Michael Valentine Jones on 05/20/2010 11:56:19
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17626 Posts

Posted - 05/20/2010 :  19:51:09  Show Profile  Reply with Quote

select  dateadd(month, datediff(month, 0, @date) - (12 + datepart(month, @date) - 10) % 12, 0),
        dateadd(month, datediff(month, 0, @date) - (12 + datepart(month, @date) - 10) % 12 + 12, -1)



KH
Time is always against us

Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 05/21/2010 :  07:31:19  Show Profile  Reply with Quote
SWEET!

Everyday I learn something that somebody else already knew
Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 04/25/2013 :  09:34:01  Show Profile  Reply with Quote
How can I use this same code to get fiscal year ( Oct-sept) to previous day? Or How can I place this same code in the WHERE clause.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/25/2013 :  12:36:57  Show Profile  Reply with Quote
Build a table. Use table.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/25/2013 :  14:14:34  Show Profile  Reply with Quote
quote:
Originally posted by sergeant_time

How can I use this same code to get fiscal year ( Oct-sept) to previous day? Or How can I place this same code in the WHERE clause.



Can you give an example of your input and expected output? what you mean by previous day...
Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 04/25/2013 :  14:42:32  Show Profile  Reply with Quote
My date range would be October 1, 2012 thru April 24, 2013.

quote:
Originally posted by MuMu88

quote:
Originally posted by sergeant_time

How can I use this same code to get fiscal year ( Oct-sept) to previous day? Or How can I place this same code in the WHERE clause.



Can you give an example of your input and expected output? what you mean by previous day...

Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/25/2013 :  14:50:43  Show Profile  Reply with Quote
What would you like to do with this date range?
What is your query?
Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 04/25/2013 :  15:08:54  Show Profile  Reply with Quote
I am trying to get the start of the fiscal year (Oct 1) to the previous business day (Ex: April 24).
quote:
Originally posted by MuMu88

What would you like to do with this date range?
What is your query?


Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 04/26/2013 :  15:30:03  Show Profile  Reply with Quote
WHERE (YOURTABLEDATE >= dateadd(month, datediff(month, 0, GETDATE()) - (12 + datepart(month, GETDATE()) - 10) % 12, 0)) AND (YOURTABLEDATE < dateadd(month, datediff(month, 0, GETDATE()) - (12 + datepart(month, GETDATE()) - 10) % 12 + 12, -1))
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000