Member Avatar for sid78669

I have a users table that has a table titled 'DOB', of format DateTime in MSSQL. Now, I would like to filter rows for all users who are over 30. till now i used this code:

SELECT * FROM [User] WHERE [User].DOB > = DATEADD(YEAR, @ageFrom, GetDate())

this statement returns nothing if i input a negative number as ageFrom and returns all rows if it is positive.

Can someone point me to correct direction?

  • 1 Contributor
  • 1 Reply
  • 139 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by sid78669
Member Avatar for sid78669

Ok, so i had two things wrong. Firstly, i was not converting the age parameter into varchar (my ignorance, sorry!). Secondly, I believe i was doing it the wrong way. After 4 hours of extensive head banging, I have been able to fix this problem. The resultant query is:

ALTER PROCEDURE [dbo].[pr_BrowseUsers]
	(
		@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] LEFT OUTER JOIN UserPreferences ON [User].USER_ID = UserPreferences.UserID WHERE 1=1 '
	
--Lower limit of age
	IF ( @ageFrom IS NOT NULL AND @ageFrom > 0 ) 
		SELECT @sql = @sql + ' AND (DATEDIFF(dd, [User].DOB, GetDate())  / 365) > = ' + CONVERT(varchar(10),@ageFrom)

--Upper limit of age
	IF ( @ageTo IS NOT NULL AND @ageTo > 0 )
		SELECT @sql = @sql + ' AND (DATEDIFF(dd, [User].DOB, GetDate())  / 365) < = ' + CONVERT(varchar(10),@ageTo)
		
-- Other code .....

	EXEC(@sql)

	RETURN

I would also put just the date part as a code snippet so that other facing the same dilemma might have it easier than i did :)

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.