SQLServerCentral / SQL Server 2005 / T-SQL (SS2K5) / DatePart week and year end / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 07:21:49 GMT20RE: DatePart week and year endhttp://www.sqlservercentral.com/Forums/Topic1405044-338-1.aspxI just came across this and created a case statement to get around this.CASE WHEN DATEPART(ww,Driver_summary.Dor_date)=53 THEN 1 ELSE DATEPART(ww,Driver_summary.Dor_date) END AS WeekNumFri, 11 Jan 2013 14:00:15 GMToctagonbiRE: DatePart week and year endhttp://www.sqlservercentral.com/Forums/Topic1405044-338-1.aspxThis calendar table might also help you out[url]http://www.sqlservercentral.com/scripts/Date/68389/[/url]Thu, 10 Jan 2013 02:13:14 GMTanthony.greenRE: DatePart week and year endhttp://www.sqlservercentral.com/Forums/Topic1405044-338-1.aspx[quote][b]pbaldy (1/9/2013)[/b][hr]I appreciate your quick reply. I will do that research. The DatePart() function in Access has arguments that let you control this (specifically the "firstweekofyear" argument). I had hoped SQL Server's did as well and I was just missing it.[/quote]try this for a start[u][b][url]http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/[/url][/b][/u]Wed, 09 Jan 2013 15:47:35 GMTJ Livingston SQLRE: DatePart week and year endhttp://www.sqlservercentral.com/Forums/Topic1405044-338-1.aspxI appreciate your quick reply. I will do that research. The DatePart() function in Access has arguments that let you control this (specifically the "firstweekofyear" argument). I had hoped SQL Server's did as well and I was just missing it.Wed, 09 Jan 2013 15:41:54 GMTpbaldyRE: DatePart week and year endhttp://www.sqlservercentral.com/Forums/Topic1405044-338-1.aspxsuggest you search this site (and others) for "Calendar tables"....weeks are generally peculiar to the industry/company...there is a lot of help out there if you care to read.regardsWed, 09 Jan 2013 15:26:18 GMTJ Livingston SQLDatePart week and year endhttp://www.sqlservercentral.com/Forums/Topic1405044-338-1.aspxI have this in a stored procedure (SS2005):DATEPART(ww,Driver_summary.Dor_date) AS WeekNumWhen run for a period of 12/30/12 through 1/5/13, it is returning 53 for the days in 2012 and 1 for the days in 2013. Is there a way to get the function to return the same week number for any given Sunday-Saturday time frame, regardless of whether it falls over a year end?Thanks,PaulWed, 09 Jan 2013 15:19:50 GMTpbaldy