Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Fiscal Year date fuction

Author  Topic 

MxWebb
Starting Member

8 Posts

Posted - 2010-05-20 : 10:39:07
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-20 : 11:41:49
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)

7020 Posts

Posted - 2010-05-20 : 11:54:10
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 19:51:09
[code]
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)
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-21 : 07:31:19
SWEET!

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

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2013-04-25 : 09:34:01
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-25 : 12:36:57
Build a table. Use table.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 14:14:34
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 - 2013-04-25 : 14:42:32
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

549 Posts

Posted - 2013-04-25 : 14:50:43
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 - 2013-04-25 : 15:08:54
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 - 2013-04-26 : 15:30:03
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

carmenv323
Starting Member

3 Posts

Posted - 2014-12-15 : 13:20:00
can someone break down this formula?

(Patient.Created >= dateadd(month, datediff(month, 0, GETDATE()) - (12 + datepart(month, GETDATE()) - 10) % 12, 0)) AND
(Patient.Created < dateadd(month, datediff(month, 0, GETDATE()) - (12 + datepart(month, GETDATE()) - 10) % 12 + 12, -1))
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-12-16 : 08:28:41
carmenv, the where filter is erraneous. You are missing a full day on the second line.

Try this
(Patient.Created >= DATEADD(MONTH, DATEDIFF(MONTH, '19001001', dt) / 12 * 12, '19001001')) AND
(Patient.Created < DATEADD(MONTH, DATEDIFF(MONTH, '18991001', dt) / 12 * 12, '19001001'))


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-16 : 09:57:00
Lamprey is correct. Don't compute these things. Build a table
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-16 : 09:58:10
See this article: http://www.made2mentor.com/2011/04/calendar-tables-why-you-need-one/
Go to Top of Page
   

- Advertisement -