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.
| Author |
Topic |
|
Gothan
Starting Member
3 Posts |
 Posted - 2007-09-07 : 04:07:04
|
| HiI have a slight problem with a date table. The problem we have is with our financial year. The financial year starts on the 1st of april each year. Now I want to use Datepart(week...) function to calculate the week number, but the problem is, sql server will calculate from the 1st of january as week 1, and april will then roughly be week 13.Is there a way to tell sql server that the "year" starts on the 1st of april.I have written various functions and tried everything and somehow the calculations just fall flat.Maybe someone has some advice that might help? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-09-07 : 04:10:23
|
[code]select datepart(week, yourdate) - datepart(week, '20070401')[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Gothan
Starting Member
3 Posts |
Posted - 2007-09-07 : 04:36:41
|
| HiThanks for the quick reply. I have tried this, and its working fine for all values after april for the same physical year for instance if we are in financial year 2007 (starts on 1 april 2007), then it works fine for may 2007, june 2007.....dec 2007.The problem is when I try a date like 2007/02/01. Its before april 2007, meaning it falls in the 2006 cycle, now I have done it dynamically to calculate the financial year based on month, but the problem is then the datepart - datepart does not work because it returns a negative value.So if you use : select datepart(week, '20070201') - datepart(week, '20060401') you get -8 and if you switch it to :select datepart(week, '20060401') - datepart(week, '20070201') you get 8 which is still incorrect |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-09-07 : 04:48:58
|
Seems a bit simplistic, but how about SELECT DATEPART(WEEK, DATEADD(MONTH, -3, GETDATE())) |
|
|
|
Gothan
Starting Member
3 Posts |
Posted - 2007-09-07 : 04:55:54
|
| AAARRRGGGHHHHHHYou did it with such a simple statement, I just wrote a huge freakin piece just to do what you did in that one line!!!Thanks for the help though |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-09-07 : 06:34:05
|
| No problem!Mark |
|
|
|
|
|
|
|
|
Using datepart(week...) if fin year starts in apr?