1/9/12

SQL Server 2012, new functions: EOMONTH

Have you missed the Oracle function LAST_DAY? Have you solved it in Microsoft SQL Server in various ways?

What we could do before the upcoming release of SQL Server is to start out with the current date, add one month, a8nd subtract the number of days of the current month. Or we could get the current date, add one month, convert that to characters, cut out the year and month part and concatenate that with the string '801', and convert that to date, and then subract one day.

Look no further, now we have the EOMONTH-function instead.

EOMONTH ( start_date [, month_to_add ] ) * start_date is the current date, or the date you want the highest date for * month_to_add is the optional increment of months

-- Prepare a variable with the current date
DECLARE @date date;
SELECT @date = getdate();

-- Method 1
SELECT DATEADD(day, (0 - datepart(day, @date)), DATEADD(month, 1, @date));

-- Method 2
SELECT dateadd(day, -1, CAST(CONVERT(varchar(8), dateadd(month, 1, @date), 120) + '01' AS date));

-- New function
SELECT EOMONTH(@date);
SELECT EOMONTH(@date, 2);
SELECT EOMONTH(@date, -4);

No comments:

Post a Comment