I have been working with dates lately and have come up with a few useful SQL Date functions lately. I find myself recreating these functions on all the projects, so I have saved below in the form of a script.
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-07-08
-- Description: Returns the number of days in a month for the passed date/time
-- ============================================================================
CREATE FUNCTION [dbo].[fncDaysInMonth]
(
@date AS DATETIME
)
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(mm, DATEDIFF(mm, -1, @date), -1))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-07-08
-- Description: Returns a date from 3 integers
-- ============================================================================
CREATE FUNCTION dbo.fncDate
(
@Year INT,
@Month INT,
@Day INT
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd, @Day - 1, DATEADD(mm, @Month - 1, DATEADD(yyyy, @Year - 1900, 0)))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-06-29
-- Description: Calculates the last day of the month
-- ============================================================================
CREATE FUNCTION [dbo].[fncLastDayOfMonth]
(
@dt DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd, -DAY(DATEADD(m,1,@dt)), DATEADD(m,1,@dt))
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-06-29
-- Description: Returns the first day of the month for a given date
-- ============================================================================
CREATE FUNCTION [dbo].[fncFirstDayOfMonth]
(
@dt DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(day, -DAY(@dt) + 1, @dt)
END
GO
-- ============================================================================
-- Author: Rob Lieving
-- Create date: 2009-02-17
-- Description: Returns a smalldatetime with the time removed
-- ============================================================================
CREATE FUNCTION [dbo].[fncRemoveTime]
(
@dt AS DATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN CAST(DATEADD(month,((YEAR(@dt)-1900)*12)+MONTH(@dt)-1,DAY(@dt)-1) AS SMALLDATETIME)
END
GO
