January 15, 2011

Finding the Week of the Month

Filed under: SQLServerPedia Syndication,T-SQL — Joe Casella @ 6:26 pm
Tags: ,

A friend of mine needed to find the week-number of the month in which a transaction occurred. A bit of searching returned several sites with examples that worked like this:

  1. DECLARE @Date datetime = ‘1/14/2011’
  2. SELECT Wk = DATEDIFF(week, CONVERT(varchar(6), @Date, 112) + ’01’, @Date) + 1
  3. Result: 3

The test date is compared to the first of the month using the datediff function with week as the datepart.  Notice how the first of the month is calculated:

  1. CONVERT(varchar(6), @Date, 112) + ’01’

Converting a date to char/varchar (8) with a style of 112 will return a string with the format yyyymmmdd.  Today, January 14, 2011,  the statement CONVERT(varchar(8), GETDATE(), 112) returns the string 20110114.  By converting to varchar(6), the sample code plays a trick and truncates the string, returning 201101. Concatenating 01 to the end of the string gives the first day of the month (20110101) and the DATEDIFF function then implicitly converts the string back to a date to perform its calculation.

It gets the job done, but it seems a little unnatural to use string manipulation to solve a date problem.

Here’s how to find the answer without changing data types:

  1. DECLARE @Date datetime = ‘1/14/2011’
  2.   SELECT  DATEDIFF(week, DATEADD(day,1, @DateDAY(@date) + 1) 1, @Date) +1
  3. Result: 3

During some months the result might be counter-intuitive. This month, January 2011 is a good example: 1/30/2011 and 1/31/2011 are considered to be week 6.  This happens because the DATEDIFF function counts “datepart boundaries crossed”. Week boundaries run from Sunday to Saturday.  The first week of this month has only one day, Sunday, January 1.  At the end of the month,  Sunday, January 30 a new, “6th” week begins.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: