THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Calculating third Wednesday of the month with inline UDFs

Problems such as calculating third Wednesday of the month or the last day of the month are very common. Recently Peter Larsson posted a scalar UDF that solves problems such as "third Wednesday of the month", and Uri Dimant added a comment with a solution by Steve Kass. Unfortunately, all these solutions use scalar UDFs and as such are very slow, so I decided to post some very fast inline UDFs that I find very useful.

 

Composing a date from year, month, and day without checking if the parameters are valid.

 

The following inline UDF composes a date. I deliberately intend to use it with invalid parameters, such as month equal to zero, because it dramatically simplifies my logic.

 

CREATE FUNCTION Readers.ComposeDate(@year INT@month INT@day INT)
-- I deliberately do not check if the parameters are valid
RETURNS TABLE AS RETURN(
  
SELECT DATEADD(DAY@day 1DATEADD(MONTH@month 1,  DATEADD(YEAR@year 1901'19010101')))
    
AS ComposedDate

GO
  

 

Calculating the last days of previous and this months.

 

Because my ComposeDate function handles out of range parameters, I don't need to handle January and December differently. Here you go, create them and see how they work:

 

CREATE FUNCTION Readers.LastDayOfPreviousMonth(@year INT@month INT)
RETURNS TABLE AS RETURN(
  
SELECT ComposedDate AS LastDayOfPreviousMonth
    
FROM Readers.ComposeDate(@year@month0)

GO
SELECT LastDayOfPreviousMonth'20081231' FROM Readers.LastDayOfPreviousMonth(2009,1)
UNION ALL
SELECT LastDayOfPreviousMonth'20090131' FROM Readers.LastDayOfPreviousMonth(2009,2)
GO
CREATE FUNCTION Readers.LastDayOfMonth(@year INT@month INT)
RETURNS TABLE AS RETURN(
  
SELECT ComposedDate AS LastDayOfMonth
    
FROM Readers.ComposeDate(@year@month+10)

GO
SELECT LastDayOfMonth FROM Readers.LastDayOfMonth(2009,11)
UNION ALL
SELECT LastDayOfMonth FROM Readers.LastDayOfMonth(2009,12)
GO 

 

Calculating third Wednesday and other similar problems

 

For simplicity (this is a blog post, not production code), this function does not determine if the returned date is actually in the required month or in another one. If you need such validation, use the approach demonstrated in the next example.

CREATE FUNCTION Readers.MthDayOfNthWeek(@year INT@month INT@week INT@day INT)
-- @day 1 Sun, 2 Mon etc.

-- this function works in US but might need adjustments in other countries
RETURNS TABLE AS RETURN
  
SELECT t.ComposedDate AS MthDayOfNthWeek
    
FROM (SELECT ComposedDate
      
FROM Readers.ComposeDate(@year@month1)) AS d
        
CROSS APPLY Readers.ComposeDate(@year@month
        
@day DATEPART(dw,ComposedDate) + 1 +
        
7*(@week CASE WHEN DATEPART(dw,ComposedDate)>@day THEN ELSE -END) ) AS t

GO
-- third Wednesday of June 2009
SELECT MthDayOfNthWeek'20090617' AS ExpectedDate 
  
FROM Readers.MthDayOfNthWeek(200963, 4)
UNION ALL
SELECT MthDayOfNthWeek'20090715' AS ExpectedDate 
  
FROM Readers.MthDayOfNthWeek(200973, 4)
UNION ALL
SELECT MthDayOfNthWeek'20090601' AS ExpectedDate 
  
FROM Readers.MthDayOfNthWeek(200961, 2)
UNION ALL
SELECT MthDayOfNthWeek'20090605' AS ExpectedDate 
  
FROM Readers.MthDayOfNthWeek(200961, 6)
UNION ALL
SELECT MthDayOfNthWeek'20090606' AS ExpectedDate 
  
FROM Readers.MthDayOfNthWeek(200961, 7)
UNION ALL
SELECT MthDayOfNthWeek'20090608' AS ExpectedDate 
  
FROM Readers.MthDayOfNthWeek(200962, 2)
 

This function is not completely trivial, so I used test driven development to come up with it.

 

Validating dates

 

If you need to validate dates, use the following approach:

 

CREATE FUNCTION Readers.ComposeValidDate(@year INT@month INT@day INT)
RETURNS TABLE AS RETURN(
  
SELECT CASE WHEN YEAR(ComposedDate@year AND MONTH(ComposedDate@month 
      
THEN ComposedDate END AS ComposedDate
    
FROM Readers.ComposeDate(@year@month@day)

GO
SELECT ComposedDate'20090228' AS ExpectedResult FROM Readers.ComposeValidDate(2009228)
UNION ALL 
SELECT ComposedDate, NULL FROM Readers.ComposeValidDate(2009229)
UNION ALL 
SELECT ComposedDate, NULL FROM Readers.ComposeValidDate(200920)
UNION ALL 
SELECT ComposedDate, NULL FROM Readers.ComposeValidDate(2009012)
  

Only the first result will validate.

 Read more about performance of inline UDFs in "Reuse Your Code with Table-Valued UDFs" and "Many nested inline UDFs are very fast"

 

Published Sunday, June 21, 2009 10:06 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Alex

Steve Kass has a similar UDF I have been using for some reports.

create function dbo.NthWeekDay(

  @first datetime,   -- First of the month of interest (no time part)

  @nth tinyint,      -- Which of them - 1st, 2nd, etc.

  @dow tinyint       -- Day of week we want

) returns datetime as begin

-- Note: Returns a date in a later month if @nth is too large

 declare @result datetime

 set @result = @first + 7*(@nth-1)

 return @result + (7 + @dow - datepart(weekday,@result))%7

end

go

-- Find the 5th Thursday of August, 2002

select dbo.NthWeekDay('2002/08/01',5,5) as D

June 22, 2009 12:46 AM
 

Alexander Kuznetsov said:

Hi Uri,

I know about Steve Kass's function, but unfortunately it is a scalar UDF. As such, it is very slow as compared to an inline UDF.

June 22, 2009 9:15 AM
 

daveballantyne said:

All very nice,  but how does this compare (both in performance and clarity ) to using a calendar table ?

June 22, 2009 9:24 AM
 

Alexander Kuznetsov said:

Dave,

I always use my calendar table for more complex cases, such as "third business day of the month" or "two business days before last business day of the month". However, typically joining with calendar table is a little bit slower than DATEADD and DATEDIFF.

IMO clarity is in the eye of the beholder, so it is difficult to argue on that.

June 22, 2009 9:46 AM
 

Alexander Kuznetsov said:

Can you give an example of a simple expression with a calendar table when you don't have to worry if you move to another month or year?

June 22, 2009 9:56 AM
 

daveballantyne said:

Ive used Cte's to break down my thinking clearly but basically you are looking for a particular day between a set weekly period , ie the Wednesday between the 15th and 22nd of the month.

My table presently has the date (dte) and a DayBitMask(1=Monday,2=Tuesday,4 = Wednesday).

Obviously the beauty of a table is that if you need to regularly reference a calculated value you can add a column for it , and add an index.  Just doing Top 1 where > Earliest date caused an index scan,  'closing' the range makes a seek.

Declare @NthDayOfMonth integer

Set @NthDayOfMonth = 3;

with MonthList(FirstOfMonth)

as

(

  Select convert(smalldatetime,'20090101')

),

FirstPossibleDay(FirstPoss)

as

(

  Select FirstOfMonth+(7*(@NthDayOfMonth-1))

    from MonthList

)

,

SeekRange(FirstPoss,LastPoss)

as

(

  Select FirstPoss,FirstPoss+7

   from  FirstPossibleDay

)

Select Dte

from Calendar,SeekRange

where Dte between FirstPoss and LastPoss

 and daymask = 4

June 22, 2009 10:47 AM
 

Alexander Kuznetsov said:

Looks good. When you post the scripts that create and populate your calendar table we shall be able to benchmark both approaches. Can you post your scripts?

June 22, 2009 11:16 AM
 

daveballantyne said:

Sorry For the length of this post ,  it would appear that the tab version does appear to perform better.

Tab  Cpu = 125 Dur = 2264

Calc Cpu = 734 Dur = 2747

CREATE FUNCTION ComposeDate(@year INT, @month INT, @day INT)

-- I deliberately do not check if the parameters are valid

RETURNS TABLE AS RETURN(

 SELECT DATEADD(DAY, @day - 1, DATEADD(MONTH, @month - 1,  DATEADD(YEAR, @year - 1901, '19010101')))

   AS ComposedDate

)

GO

CREATE FUNCTION MthDayOfNthWeek(@year INT, @month INT, @week INT, @day INT)

-- @day 1 Sun, 2 Mon etc.

-- this function works in US but might need adjustments in other countries

RETURNS TABLE AS RETURN(

 SELECT t.ComposedDate AS MthDayOfNthWeek

   FROM (SELECT ComposedDate

     FROM ComposeDate(@year, @month, 1)) AS d

       CROSS APPLY ComposeDate(@year, @month,

       @day - DATEPART(dw,ComposedDate) + 1 +

       7*(@week + CASE WHEN DATEPART(dw,ComposedDate)>@day THEN 0 ELSE -1 END) ) AS t

)

GO

Drop Table Calendar

go

Create Table Calendar

(

Dte smalldatetime primary key,

Year  integer not null,

Month tinyint not null,

Day   tinyint  not null,

NThDayOfMonth tinyint not null,

DayOfWeek     tinyint not null

)

go

Create Unique index idxCalendar on Calendar(Year,Month,NThDayOfMonth,DayOfWeek )include(dte)

go

Declare @PopDate smalldatetime

Select @PopDate ='20000101'

while(@PopDate <='20501230') begin

  insert into Calendar values(@PopDate,DatePart(yy,@PopDate),DatePart(mm,@PopDate),DatePart(dd,@PopDate),

                                       (DatePart(dd,@PopDate)/7)+1,DatePart(dw,@PopDate))

  Select @PopDate =@PopDate+1

end

go

update statistics Calendar with fullscan

go

Create Function TabMthDayOfNthWeek(@year INT, @month INT, @week INT, @day INT)

RETURNS TABLE AS RETURN(

 Select dte from Calendar Where Year = @Year and Month =@Month and NthDayOfMonth = @week and DayOfWeek = @Day

)

go

select dte

 from sysobjects s1 cross apply

      sysobjects s2 cross apply

      tabMthDayOfNthWeek(2009, 6, 3, 4)

go

select MthDayOfNthWeek

 from sysobjects s1 cross apply

      sysobjects s2 cross apply

      MthDayOfNthWeek(2009, 6, 3, 4)

June 23, 2009 4:21 AM
 

Alexander Kuznetsov said:

Dave,

I am getting similar results - in this case precalculating makes perfect sense. Thanks!

June 23, 2009 10:57 PM
 

Peso said:

Faster ComposeDate. Only one function instead of three...

CREATE FUNCTION Readers.ComposeDate(@year SMALLINT, @month TINYINT, @day TINYINT)

RETURNS TABLE AS RETURN(

 SELECT DATEADD(MONTH, 12 * @Year - 22801 + @month, @day - 1) AS ComposedDate

) GO

July 8, 2009 9:43 AM
 

Peso said:

Other faster calculations of LastDay

CREATE FUNCTION Readers.LastDayOfPreviousMonth(@year INT, @month INT)

RETURNS TABLE AS RETURN(

 SELECT DATEADD(MONTH, 12 * @Year - 22801 + @month, -1) AS LastDayOfPreviousMonth

) GO

CREATE FUNCTION Readers.LastDayOfMonth(@year INT, @month INT)

RETURNS TABLE AS RETURN(

 SELECT DATEADD(MONTH, 12 * @Year - 22800 + @month, -1) AS LastDayOfMonth

) GO

July 8, 2009 9:47 AM
 

Alexander Kuznetsov said:

Peso,

The following is the opinion of Tibor Karaszi:

"First of all, my issue with that type of hack is not with the second argument to the DATEADD function, but with the third argument. Check out BOL, DATEADD. The expression used in the comment suggestion for the third argument evaluates to an tinyint. I don’t see in the BOL text for DATEADD that the third parameter (“date”) can be tinyint.

I’ve had this issue a lot when I advocated supported measures to “strip out” the time options (setting it to 00:00) when dealing with datetime. My argument was that if you use something which depend on the internal representation of datetime, you will probably be shot in the foot some day. Many people were irritated by this viewpoint and wanted to use “hacks” – they thought I was being overly pedantic and I was thinking about things that just won’t happen. Well, whaddoyaknow: SQL 2008 has new date and time types with a vastly different internal representation.

So, having typing this much into this email, I haven’t even looked at the logic behind the proposal, I just zoomed in on that third argument to DATEADD… So, I’m now jumping back to SSMS and try to understand what this function actually do (how it work)…

.. OK, just the fact that I had to sit and think for a while to decipher what this does makes the function, IMO, bad. Readibility and understandeability. And the number 22801 (divide by 12 and you get 1900 – the base year for datetime and smalldateime) definitely has some bearing to the internal representation of datetime. As far as I can tell, the fact that the 3rd parameter isn’t according to specifications (Books Online doesn’t say it accepts any int type), causes the seconds parameter with the calculation based on the internal base date automatically becomes equally unsupported. "

July 9, 2009 11:32 AM
 

Marlene said:

Hi

I want to determine the second working day of the month. Also considering public holidays. So say the first working day of the month is a public holiday, the actual first working day would be the next day and the second working day, 2 days later. i.e. First working day is a Monday, but it is a public holiday too.. so the first working day is the Tuesday and the second working day is the Wednesday. Is there an easy automated way to determine this?  Any help would be much appreciated.

Many Thanks

Marlene

August 26, 2010 9:23 AM
 

Alexander Kuznetsov said:

Marlene,

The easiest way to accomplish this is to use a calendar table, in which all business days have consecutive numbers.

For example, a scalar UDF that adds business days can look like this:

CREATE FUNCTION [Readers].[AddBusinessDays](@d DATETIME, @dayChange INT, @calendarType VARCHAR(10))

RETURNS DATETIME

AS

BEGIN

DECLARE @ret DATETIME, @dayNumber INT

SET @dayNumber = (SELECT NumBusinessDay FROM data.BusinessDates WHERE CalendarType = @calendarType AND DateFrom = @d) + @dayChange

SET @ret = (SELECT DateFrom FROM data.BusinessDates WHERE CalendarType = @calendarType AND NumBusinessDay = @dayNumber AND IsBusinessDay = 'Y')

RETURN @ret

END

August 26, 2010 12:28 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement