TSQL - MonthEnd

[xrapidx]

Ok...here's the problem, I have a table with a couple of columns, two of which are MonthEnd & TransactionDate

This table loads daily with about 70mil records, MonthEnd is not available, and needs to be calculated from the TransactionDate, whats the quick way of doing this? e.g. If the TransactionDate is '2008/01/24' the MonthEnd dte will be '2008/01/31'. Anyone know how to do this quickly?

Currently I have the code below, but its taking way longer than I'd like.

UPDATE Transaction.Detail
SET MonthEnd = dateadd(dd, -1 ,dateadd(mm,1+datediff(mm,0,TransactionDate),0))

 

[EtienneK]

To get the amount of days in the month... why don't you just use a case statement? Like, if the month = 1 return 31

pseudo code (not t-sql code):

switch DATEPART(mm, TransactionDate)
begin
   case 1: return 31
   case 2: return 28 // Add another if statement here to check for leap years
   case 3: return 31
   case 4: return 30
   // ... etc ...
end

To get the year and month use DATEPART(yy, TransactionDate) and DATEPART(mm, TransactionDate), respectively.

I don't know if this'll be faster. I'm just throwing ideas around.