SQL – End of Month date

A useful function to have is a function that turns any date into an end of month date. That is, take today’s date and turn that the date of the month-end. Nilssonhedge’s data is mostly for month-ends, but we may occasionally use daily data. This is similar to the Excel EOMDATE function.

CREATE  FUNCTION [dbo].[EomDate] ( @date datetime )
RETURNS datetime

AS
BEGIN

RETURN dateadd(day, -1, dateadd(month, 1, CONVERT(datetime,LEFT(convert(varchar(10), @date,120),7) + '-01' ,120)))


END

The algorithm to work by isolating the year and month from a string. Converting that date to the first of the month, adding a month and then finally subtracting one day.

Advertisements
%d bloggers like this: