Please see my code snippet.
When running this Function based on today (31/03/2009 which is this months end date) using GETDATE() as follows:
I get the 30/03/2009 and I get a time value.
I would like to retrieve 00:00:00 time value, which in tern may sort out the incorrect date, but if it doesn't that needs to be allowed for as well where your running the function on the last day of the month.
I love dynamic things, but sure can be a pain in the head sometimes!
CREATE function [dbo].[wfThisMonthEndDate] (@today as smalldatetime)
Returns smalldatetime AS
DECLARE @datestring AS VARCHAR(10)
DECLARE @date SMALLDATETIME;
SET @datestring = CONVERT(VARCHAR(10),@today,103)
SET @date = @datestring
DECLARE @start SMALLDATETIME, @endDay SMALLDATETIME;
SET @start = DATEDIFF(DAY,0,@date);
SET @endDay = DATEADD(MONTH,1,@date)-DAY(@start);
SET @datestring = CONVERT(VARCHAR(10),@endday,111)