Storing the age of an individual in a Users or Employees table is not a good database design simply because this changes every year. You don't want to update the database every day (or every week) just to update the age. In place of the age, the birth date of the individual should be stored instead.
Given the birth date of the individual, the age can easily be computed. The user-defined function below computes the age an individual for any given day given the birth date. CREATE FUNCTION [dbo].[ufn_GetAge] ( @pDateOfBirth DATETIME,
@pAsOfDate DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @vAge INT
IF @pDateOfBirth >= @pAsOfDate
RETURN 0
SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)
IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR
(MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND
DAY(@pDateOfBirth) > DAY(@pAsOfDate))
SET @vAge = @vAge - 1
RETURN @vAge
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