funooni (7/19/2010)
...
Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.
One more thing to add the function DATEPART(WEEK,<ANY NUMBER FROM 0-364>) will return the current week number.
For instance,
select DATEPART(WEEK,21)will return 4 as 3 weeks have finished.
Let me know please if this does not work and correct me if i am wrong at perceiving this.
Thanks
You are wrong at perceiving how DATEPART function works!
And it works exactly as explained by Paul White NZ.
Check the BoL.
The second input parameter of DATEPART function is of DATETIME datatype.
When you supply the integer there, it is implicitly converted into datetime as per following:
1 Jan 1900 + INT value.
You can supply positive or negative integer values as you wish...
The number of week will properly match only for years which week days match ones of year 1900! Last such year was 2007 and the next one will be 2018!
So if you run
select DATEPART(WEEK,364)
it will return 53 always, regardless when (which year) you run it as it represents 31 Dec 1900!
But correct week number for 31 Dec 2000 would be 54 - leap year man!
select DATEPART(WEEK,'31 Dec 2000')