How can i get the first day of the month and the last day of the month?

Is there a function that do id? or i need to calculate it by myself?Hi, there is no built in one, but you can use these ones here which is just wrote for you:

CREATE FUNCTION FirstDayofMonth

(

@.InputDate DATETIME

)

RETURNS DateTime

AS

BEGIN

RETURN (SELECT LEFT(CONVERT(VARCHAR(10),@.Inputdate,112),6) + '01')

END

GO

SELECT dbo.FirstdayofMonth(GETDATE())

CREATE FUNCTION LastDayofMonth

(

@.InputDate DATETIME

)

RETURNS DateTime

AS

BEGIN

RETURN (SELECT DATEADD(dd,-1,DATEADD(mm,+1,LEFT(CONVERT(VARCHAR(10),@.InputDate,112),6) + '01')))

END

GO

SELECT dbo.LastDayofMonth(GETDATE())

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||You can write scalar UDFs to do this like Jens showed. But if you need more functionality like determining holidays, different week numbers, tracking different calendars (fiscal, financial...) etc it is easier to build a static calendar table that contains all the dates. You can them simply query the calendar table to get the necessary information. This is a common approach in data warehousing environment to create a date or calendar dimension.|||As good start to read for that is:

http://www.aspfaq.com/show.asp?id=2519

HTH; jens Suessmeyer.

http://www.sqlserver2005.de
|||select dateadd(month, datediff(month, 0, getdate()), 0) as [First Day of the Month],
dateadd(month, datediff(month, 0, getdate()), -1) as [Last Day of the Month]|||I think that wont give the proper results, a slight change will help

select dateadd(month, datediff(month, 0, getdate()), 0) as [First Day of the Month],
dateadd(month, datediff(month, -1, getdate()), -1) as [Last Day of the Month]