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.