thread183-1120970 Last Day of Month

[mlibeson]

In a previous thread (thread183-1120970) I obtained some information that helped me to get the last day of a month if I provided a date.

Here is the function I created in MSSQL:

CREATE FUNCTION fn_GetLastDayOfMonth (@date DATETIME)
RETURNS DATETIME AS
BEGIN
     SET @date = DateAdd(month,1,@date);
     SET @date = CONVERT(CHAR(10), CAST(MONTH(@date) AS VARCHAR(2)) + '/01/' + CAST(YEAR(@date) AS VARCHAR(4)), 101);
     SET @date = DATEADD(d, -1, @date)
     RETURN @date;
END
[code]

I hope this helps you.


Michael Libeson

 

[SQLDenis]

--The first day and last of the current month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+0, 0)
SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0))

--The first day of any month
declare @d datetime
select @d ='20050214'
SELECT DATEADD(mm, DATEDIFF(mm, 0, @d)+1, 0)


--The last day of any month
declare @d datetime
select @d ='20050214'

SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(mm, 0, @d)+1, 0))


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Then your function would be

CREATE FUNCTION fn_GetLastDayOfMonth (@date DATETIME)
RETURNS DATETIME AS
BEGIN
     SET @date = DATEADD(d,-1,DATEADD(mm, DATEDIFF(mm, 0, @date )+1, 0))

     RETURN @date;
END

select dbo.fn_GetLastDayOfMonth('20050214')



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/