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.ThanksMichael 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.Jimdeclare @basedate datetimedeclare @date datetimedeclare @startdate datetimedeclare @enddate datetimeset @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) endset @enddate = dateadd(year,1,@startdate) - 1select @startdate,@enddate Everyday I learn something that somebody else already knew |
|
|
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-30CODO ERGO SUM |
|
|
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] |
|
|
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 |
|
|
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. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-25 : 12:36:57
|
Build a table. Use table. |
|
|
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... |
|
|
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...
|
|
|
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? |
|
|
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?
|
|
|
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)) |
|
|
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)) |
|
|
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 |
|
|
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 |
|
|
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/ |
|
|
|