Get Number of Days in a Month Function
CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@pDate) % 4 = 0 AND
YEAR(@pDate) % 100 != 0) OR
(YEAR(@pDate) % 400 = 0)
THEN 29
ELSE 28
END
END
END
GO
Method 2
CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
ELSE [dbo].[ufn_IsLeapYear] ( @pDate ) + 28
END
END
GO
Method 3
CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
SET @pDate = CONVERT(VARCHAR(10), @pDate, 101)
SET @pDate = @pDate - DAY(@pDate) + 1
RETURN DATEDIFF(DD, @pDate, DATEADD(MM, 1, @pDate))
END
GO
No Of Days in an Year
CREATE FUNCTION [dbo].[ufn_GetDaysInYear] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @IsLeapYear BIT
SET @IsLeapYear = 0
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
SET @IsLeapYear = 1
RETURN 365 + @IsLeapYear
END
GO
Label List
- Architecture
- Art of SQL Server
- asynchronous mirroring
- csv
- dmv
- find outdated Statistics
- Free SQL Server tools
- index fragmentation
- Installation
- Katmai
- Learn SQL Server 2005
- Maintenance Plan
- outdated Statistics
- Reset Identity column
- Scripts
- SPID
- sql server 2008
- sys.dm_db_index_physical_stats
- sys.dm_exec_connections
- tsql
- update statistics