There will be times when you will need to determine the number of days in a year. This can be the case when you need to compute for the interest of savings accounts in banks. This should be simple enough because it is either 365 days if it is not a leap year and 366 if it is a leap year. Now the problem is determining if a year is a leap year or not. Currently, there is no date function within SQL Server that determines if a year is a leap year or not.
The user-defined function below determines whether a particular date is of a leap year or not. It takes a DATETIME parameter and return a bit flag, which determines if the date is within a leap year or not. A return value of 1 means that it is a leap year while a return value of 0 means it is not a leap year.
CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate DATETIME )
RETURNS BIT
AS
BEGIN
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
RETURN 1
RETURN 0
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