Wednesday, 2 January 2013

how to get week number for a date based on month from the date in sql server

 
We have a date column in a table and I want to get week number for that particular date based on the month from that date irrespective of the day

For example:
01-dec-2012 to 07-dec-2012 should give week number as 108-dec-2012 to 14-dec-2012 should give week number as 215-dec-2012 to 21-dec-2012 should give week number as 3
 
 
You can use DAY (Transact-SQL)  
 
select ((day(ColumnName)-1) / 7) + 1 as W
 
Example :
 
select ((day('2012-11-01 00:00:00.000')-1) / 7) + 1 as W
 
Output :
 
W
--
1

No comments:

Post a Comment