Calculating Age using Date of Birth and Current Date

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

Contributor

Replies

Views

Hello all members,
I am new to this this website. I am working on an assignment and I have a scenario where I have to find the key attributes, entities ...

I receive an error message and I don't know what to do about it.
IO have a delphi app using a MS Access 2010 database. I connect to it through ...

Hi every one .
i need code for how i set the connection i.e when i execute the project .exe it show me a form in which there is Database ...