| Author |
Topic |
|
DENIZ3E
Yak Posting Veteran
56 Posts |
 Posted - 2004-12-27 : 17:16:06
|
| HISET DATEFIRST 1SELECT DATEPART(WW,'03.30.2005')in the result=14, but it must be 13 because the date is the 13. week of the year |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-27 : 17:19:09
|
| You probably aren't counting the first week in 2005. January 3rd, which is the first Monday in January of 2005, is in week 2. You probably started counting January 3rd as week 1.Tara |
 |
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2004-12-27 : 17:34:32
|
| Where I work for pay purposes Saturday is first day of the week.SET DATEFIRST 6 -- Saturday as first day of weekSELECT DATEPART(WW,'03.30.2005') -- = 13SELECT (@@DATEFIRST + DATEPART(DW,'03.30.2005') - 1) % 7 -- = 3 -- WednesdaySELECT (@@DATEFIRST + DATEPART(DW,'01.01.2005') - 1) % 7 -- = 6 -- SaturdaySELECT (@@DATEFIRST + DATEPART(DW,'01.03.2005') - 1) % 7 -- = 1 -- MondaySELECT (@@DATEFIRST + DATEPART(DW,'01.05.2005') - 1) % 7 -- = 3 -- WednesdayEdit: Added more days; note my code above returns 0 for Sunday.Code below return Sunday as = 7SELECT (@@DATEFIRST + DATEPART(DW,'01.02.2005') - 2) % 7 + 1 -- = -- SundayTim S |
|
|
|
DENIZ3E
Yak Posting Veteran
56 Posts |
Posted - 2004-12-27 : 17:44:55
|
| In our country the first day of the week is monday. how can I CHANGE THIS? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-27 : 17:46:17
|
| You already did with:SET DATEFIRST 1Tara |
|
|
|
DENIZ3E
Yak Posting Veteran
56 Posts |
Posted - 2004-12-27 : 17:49:09
|
| BUT I CAN'T DO IT.SET DATEFIRST 1RESULT=14 BUT WRONG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-27 : 17:53:16
|
Maybe you missed this in SQL Server Books Online about DATEPART:quote:
The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
Tara |
|
|
|
npp
Starting Member
4 Posts |
Posted - 2005-01-04 : 04:51:13
|
quote:
Originally posted by tduggan
Maybe you missed this in SQL Server Books Online about DATEPART:quote:
The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
Tara
I didn't miss that part, but my problem is that in my country January 1 2005 is in week 53 and week 1 starts with Monday January 3 2005.Is there any way to make SQL server understand this?/Nikolaj |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-04 : 08:18:04
|
| I'd suggest looking at DateDiff() then. You can compare a date to a starting date and it will give you a week number that you can tune to your own needs. See Books Online for details on DateDiff(), and also look at DateAdd(). |
|
|
|
npp
Starting Member
4 Posts |
Posted - 2005-01-04 : 08:22:14
|
| I know I can probably calculate the week with some custom function, but is there no way of changing the default behavior of SQL server? Some setting like "SET DATEFIRST", but regarding the week instead like "SET WEEKFIRST" or something? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-04 : 08:28:43
|
| No. What's wrong with using the functions as described? They're a lot more flexible anyway.Not sure what you're complaining about, the behavior is perfectly logical to me. The week number is based on the start of the year, which in January 1, and has nothing to do with the previous year or the day of the week on which it falls. Any deviation from that requires some custom logic. |
|
|
|
npp
Starting Member
4 Posts |
Posted - 2005-01-04 : 08:33:44
|
| That behavior is not logical to anyone living in my country unfortunately. That is like saying it is logical that weeks start on sundays... that is logical to some, but not to others.I was hoping it was something you could set with some regional setting or something.What I'm complaining about is that I will have to make changes in a lot of someone elses code to find out where it uses SQL servers builtin method of calculating the weeknumber and replace that with some custom function. |
|
|
|
bjh
Starting Member
14 Posts |
Posted - 2005-01-04 : 08:36:22
|
| Microsoft wrote this:CREATE FUNCTION ISOweek (@DATE datetime)RETURNS intASBEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 - DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') --Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) BEGIN SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 END --Special case: Dec 29-31 may belong to the next year IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) BEGIN SET @ISOweek=1 END RETURN(@ISOweek)END-------------------------------------------------------------------------------I found it randomly on a page that describes how to create a function!!http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp |
|
|
|
npp
Starting Member
4 Posts |
Posted - 2005-01-04 : 08:39:22
|
Thanks alot bjh. That saves me some time at least. Now I will just have to find out where I have to replace the default behavior with the custom one  |
|
|
|
Gsue
Starting Member
1 Post |
Posted - 2005-01-14 : 09:31:20
|
| Hi, I have facing the same problem with week counting. Anyway, the set datefirst procedure is useful :), but has anyone found any permanent "system" solution to the problem? Gsue |
|
|
|
bohacik.robo
Starting Member
1 Post |
Posted - 2005-01-18 : 03:08:52
|
When week begin on the monday in your country then you can use expression that returns the date of the previous Monday if the date is not a Monday, and the date value itself if it is a Monday.date-(datepart(dw,date)-1)example:select date, date-(datepart(dw,date)-1) from abc order by date |
|
|
|
|
week