How do I use a UDF with a select statement?

Question

I created a scalar user defined function that will accept a date of birth and return the age a person will be in 15 years from now.

CREATE FUNCTION AgeIn15 (@DateOfBirth DATE) RETURNS INT AS BEGIN RETURN Convert(INT, DateAdd(year, 15, DateDiff(year, @DateOfBirth, GetDate()))) END

Now I want to Use the UDF created to show the age of all persons in my table marketing_list along with their original information. How do I do this? I tried this but I got an error

SELECT * FROM marketing_list AgeIn15(marketing_list.DateOfBirth) SELECT * ,AgeIn15(marketing_list.Date_of_Birth) FROM marketing_list WHERE AgeIn15(Date_of_Birth) > 45

Lukasz Szozda · Accepted Answer · 2015-09-29 05:16:32Z

Add schema name:

SELECT *, dbo.AgeIn15(m.Date_of_Birth) AS col_name FROM marketing_list AS m; SELECT * ,dbo.AgeIn15(m.Date_of_Birth) AS col_name FROM marketing_list m WHERE dbo.AgeIn15(m.Date_of_Birth) > 45

Optionally you can use CROSS APPLY:

Select * from marketing_list AS m CROSS APPLY (SELECT dbo.AgeIn15(m.Date_of_Birth)) AS c(col_name)

Demo

SELECT *, dbo.AgeIn15(t.c) AS col_name FROM (SELECT '2000-01-01') AS t(c); SELECT * FROM (SELECT '2000-01-01') AS t(c) CROSS APPLY (SELECT dbo.AgeIn15(t.c)) AS c(col_name)

Your function should looks like:

CREATE FUNCTION dbo.AgeIn15 (@DateOfBirth DATE) RETURNS INTEGER AS BEGIN RETURN DATEDIFF(year,@DateOfBirth,GETDATE()) + 15 END

What you did doesn't make any sense:

Convert(integer,DateAdd(year,15,DateDiff(year,@DateOfBirth,GetDate())))

Calculate DATEDIFF - it returns number years OK
DATEADD(year, 15, number) - it returns date 15 years + of 1900-01-number FAIL
CONVERT(INTEGER, DATE) - it returns number of days starting at 1900-01-01 FAIL

Thanks..I tried the first one but I'm getting ages starting from 5493 when the person was 15 yrs old..should be getting 30
@user2127184 Your function doesn't make sense. I rewrite it and make new demo
Hey please bare with me but this is the first I'm seeing this syntax so I don't know what the 't' and 'c' means. I see derived table and column. I want to display age along with all the information in my table marketing_list
@user2127184 t and c are just aliases, you could use any name that is valid identifier
@user2127184 See first two queries, they are yours, demo is to show how it works only

ken lacoste · Accepted Answer · 2015-09-29 05:14:15Z

CREATE FUNCTION AgeIn15 (@DateOfBirth DATE) RETURNS INT AS BEGIN RETURN -- get the year today + 15 years ( (DATEPART(YEAR, GETDATE() ) + 15) - -- minus the year of date of birth DATEPART(YEAR,@DateOfBirth) ) -- its like saying (2015 + 15)-09-29 - (1990)-09-19 = 40 END SELECT * , dbo.AgeIn15(mlist.Date_of_Birth) AS [AgeAfterFifteenYears] FROM marketing_list AS mlist

hopefully this helps

Collectives™ on Stack Overflow

How do I use a UDF with a select statement?

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related