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 |
|
TheChange
Starting Member
3 Posts |
 Posted - 2008-02-24 : 12:12:20
|
| hi, how doin ??in fact i found a problem with DATENAME function. i execute this select to return the week number, i change only the year part, like that :SELECT DATENAME(wk,'03/01/1993') --> Result = 2SELECT DATENAME(wk,'03/01/1994') --> Result = 2SELECT DATENAME(wk,'03/01/1995') --> Result = 1SELECT DATENAME(wk,'03/01/1996') --> Result = 1SELECT DATENAME(wk,'03/01/2000') --> Result = 2SELECT DATENAME(wk,'03/01/2001') --> Result = 1...... etcthe result must be '1', but i don't know why it returns '2'can someone help me plz ??????? thank u |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-24 : 12:19:38
|
| Why do you think they are wrong? It returns only correct result. 03/01/1993 is actually sunday which is start of 2nd week and similarly 03/01/1994 is actually Monday of the 2nd week. Please keep in mind that wk returns the week number of calendar and not based on every 7 days of month. the overlapping weeks with previous & next months are also considered. |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-02-24 : 12:27:31
|
There are issues at the end of a calendar year based where the Dec 31st falls and is affected by a few different factors. In some cases the 1st-3rd of a month may be in week 52 of the prior 52 week period. (lets say the week starts on Monday and goes to Sunday. If the 1st is on Friday, the 3rd would be on Sunday.If you are looking for March 1st, it would return 9's and 10s since it is the week of the year. So it looks like you are looking for the 1st week of the year based on January 3rd of each year and your dates are in the DD/MM/YYYY format.Check this linkhttp://www.mssqltips.com/tip.asp?tip=1422There are other ways to possible get what you are after...search this site for DATETIME functions...lots of ways to go.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
TheChange
Starting Member
3 Posts |
Posted - 2008-02-24 : 13:01:09
|
| i thought that 'wk' means the 7 days of month. i was wrongmy goal is to have weeks per year like that :Week1, week2.....if i use DATENAME i'll have Week1 (Sunday to Saturday) as u noticed, but my goal is to have 7 days. for exampleWeek1 = '01/01/2008' to '07/01/2008'Week2 = '08/01/2008' to '14/01/2008'is there any idea to get that ??tnx so much for the answer |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-02-24 : 13:36:31
|
You should read up on the datepart functions as weeknumber means exactly the weeknumber of the year..You would have to customize a function perhaps to clarify what you want out..Case when day([datecolum]) <=7 then 1 when day([datecolum]) <=14 then 2 when day([datecolum]) <=21 then 3 when day([datecolum]) <=28 then 4 else 5end
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
TheChange
Starting Member
3 Posts |
Posted - 2008-02-24 : 15:59:51
|
tnx for the replyquote:
Originally posted by dataguru1971
You should read up on the datepart functions as weeknumber means exactly the weeknumber of the year..You would have to customize a function perhaps to clarify what you want out..Case when day([datecolum]) <=7 then 1 when day([datecolum]) <=14 then 2 when day([datecolum]) <=21 then 3 when day([datecolum]) <=28 then 4 else 5end
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
|
|
|
|
|
DATENAME function