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 |
|
daviddeldave
Starting Member
28 Posts |
 Posted - 2002-01-02 : 11:05:01
|
| I know you can do this with the case statement but is it possible to specify the starting date as April 1st when using the datepart functioneg I want select datapart(q,'april 6 2001') as quarter to say 1 instead of 2. I know the set datefirst can be used to set the first day of the week but is there an equilivent to set when the first quarter should start??Appreciated DavidDavid Mercer |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-02 : 11:16:25
|
| I think you need to use a CASE statement. DatePart is an internal SQL function and I don't think you can change it. |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-02 : 11:19:22
|
| How About thisselect datediff(q,'april 1 2001',yourdate)+1 as quarter this should do what you want.----------------------------Anything that Doesn't Kills you Makes you Stronger |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-02 : 11:26:55
|
That works for quarters 1,2,3 but gives you a problem in quarter #4. Then you've got to include additional logic.quote:
How About thisselect datediff(q,'april 1 2001',yourdate)+1 as quarter this should do what you want.----------------------------Anything that Doesn't Kills you Makes you Stronger
|
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-02 : 11:29:18
|
| i did tested it and it works fyn for 4 too.----------------------------Anything that Doesn't Kills you Makes you Stronger |
|
|
|
daviddeldave
Starting Member
28 Posts |
Posted - 2002-01-02 : 11:54:34
|
Think you got the wrong idea here I want the quarter date to be converted to the quarter that the month falls under ie if jan then it should be 4 if dec then 3. Americans use January as the start of their financial year but us british use April hence april would be quarter 1 for me I just wondered if I am forced to use a case statement I dont see that I should as the datepart function See function above does return Jan as quarter 1.Is it a language setting I need to alter???Sorry if I am confusing you!!!quote:
i did tested it and it works fyn for 4 too.----------------------------Anything that Doesn't Kills you Makes you Stronger
David Mercer |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-01-02 : 12:21:12
|
| (DATEPART(q,'april 6 2001')+2)%4 + 1 |
|
|
|
|
|
|
First Quarter in Year Start at Apr instead of Jan