Calculating Age using Date of Birth and Current Date

14 Years Ago sid78669 0 462 Views

This code demonstrates how age might be calculated and filtered using the DateDiff method in SQL. Also, note that both the parameters are set to NULL by default, hence making them 'optional' to a certain limit.

Create PROCEDURE [dbo].[pr_AgeFiltering]
	(
		@ageFrom int = NULL,
		@ageTo int = NULL
	)

AS
	DECLARE @sql varchar(2000)
	SELECT @sql = 'SELECT CONVERT(VARCHAR, [User].USER_NAME, 120) AS NAME, CONVERT(VARCHAR, [User].DOB, 120) AS DOB, (DATEDIFF(dd, [User].DOB, GetDate())  / 365) AS Age FROM [User] WHERE 1=1 '
	
-- Set the Lower limit for age (optional)
	IF ( @ageFrom IS NOT NULL AND @ageFrom > 0 ) 
		SELECT @sql = @sql + ' AND (DATEDIFF(dd, [User].DOB, GetDate())  / 365) > = ' + CONVERT(varchar(10),@ageFrom)

--Set the upper limit for age (optional)
	IF ( @ageTo IS NOT NULL AND @ageTo > 0 )
		SELECT @sql = @sql + ' AND (DATEDIFF(dd, [User].DOB, GetDate())  / 365) < = ' + CONVERT(varchar(10),@ageTo
	
--Execute the SQL query
EXEC(@sql)

	RETURN

Be the first to reply

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.