Tuesday, March 2, 2010

TSQL : Custom date functions

Really simple these, but posting them incase someone googles for them / i forget them...

I'm using DATETIME and rounding to the last second of the period in question , but you could quickly change that.

CREATE FUNCTION dbo.LastDayOfMonth (@inputdate DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@inputdate)+1,0))
END
GO

The results -
SELECT dbo.LastDayOfMonth (GETDATE())
2010-03-31 23:59:59.000

SELECT dbo.LastDayOfMonth ('2010-02-03')
2010-02-28 23:59:59.000

CREATE FUNCTION dbo.LastDayOfWeek (@inputdate DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@inputdate)+1,0))
END

The results -
SELECT dbo.LastDayOfWeek ('2010-02-03')
2010-02-07 23:59:59.000

As default, my week starts Monday and ends on Sunday.
To change this, use SET DATEFIRST

No comments: