# Thread: calculate Age Sql 2005

1. Registered User
Join Date
Jun 2011
Posts
5

## Unanswered: calculate Age Sql 2005

Hi

I want to calculate age between two dates.
E.g
ID DOB
22 01-01-2009
23 04-04-2010
34 05-05-2010

Reporting period between 01-04-2011 and 30-06-2011.

Can someone assist me, I want to know how clients are age 1 between the reporting period.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by HUS
I want to know how clients are age 1 between the reporting period.
huh?

could you please rephrase the question?

3. Programming since 1BC
Join Date
Sep 2009
Location
Ontario
Posts
1,057
Convert dates to yyyymmdd.
if startdate-dob <10000 and enddate -dob >10000 then dob is within range.
You may have to test for dob on startdate and enddate.
Last edited by kitaman; 07-13-11 at 17:53. Reason: reversed < and >

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```--  ptp  20110713  Calculate the delta between two dates

CREATE FUNCTION dbo.DateDelta(@pd1 DATETIME
,  @pd2 DATETIME)
RETURNS @retDeltas TABLE
(
years		INT		NOT NULL
,  months		INT		NOT NULL
,  days			INT		NOT NULL
)
AS
BEGIN
DECLARE @iMonths		INT
SET @iMonths = DateDiff(MONTH, @pd1, @pd2)

SET @iMonths = @iMonths
+     CASE
WHEN DateAdd(month, @iMonths, @pd1) < @pd2
THEN CASE WHEN @pd1 < @pd2 THEN 0 ELSE 1 END
ELSE CASE WHEN @pd1 < @pd2 THEN -1 ELSE 0  END
END

INSERT INTO @retDeltas
SELECT @iMonths / 12
,        @iMonths % 12
,        DateDiff(DAY, DateAdd(month, @iMonths, @pd1), @pd2)

RETURN
END
GO

--  Prove we got it correct for past dates

SELECT *
FROM (SELECT DateAdd(day, -5 * number, GetDate()) AS d
FROM master.dbo.spt_values
WHERE  'p' = type) AS z1
CROSS APPLY dbo.DateDelta(d, GetDate())

--  Prove we got it correct for future dates

SELECT *
FROM (SELECT DateAdd(day, -5 * number, GetDate()) AS d
FROM master.dbo.spt_values
WHERE  'p' = type) AS z1
CROSS APPLY dbo.DateDelta(GetDate(), d)```
-PatP
Last edited by Pat Phelan; 07-13-11 at 20:16. Reason: Cleaned up delta comp and formatting

5. Registered User
Join Date
Nov 2004
Posts
1,428
Originally Posted by Pat Phelan
Code:
```SELECT *
FROM (SELECT DateAdd(day, -5 * number, GetDate()) AS d
FROM master.dbo.spt_values
WHERE  'p' = type) AS z1
CROSS APPLY dbo.DateDelta(d, GetDate())```
It's been a long time since I last saw a CROSS APPLY used, in this forum. It is an operator that I have never used before. I did a Google search on it, but I find the explanations confusing. Can you explain it a bit or direct me to a good source of information?

6. Registered User
Join Date
Mar 2007
Location
Holmestrand, Norway
Posts
332
Basically, cross apply is the same as an inner join (and as such it should be called inner apply instead). Let me try to explain:

We have a function returning a recordset, and we want to call it for each row in a table with one of the table columns as parameter, and join the row data and the function result with an "inner join". This is what the cross apply operator does.

Similarily, we have an outer apply which does the same, but always returns the data from the table, even if the function does not return anything (like an outer join).

In this example, we have a "table" z1 with a column d, and we want to join (that is cross apply) with the function datedelta, using this column as the first parameter.

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by Wim
It's been a long time since I last saw a CROSS APPLY used, in this forum. It is an operator that I have never used before. I did a Google search on it, but I find the explanations confusing. Can you explain it a bit or direct me to a good source of information?
The Microsoft doc gives what I think is a good description at Using APPLY

The paragraph just before the code sample says:
Originally Posted by SQL BOL
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
-PatP

8. Registered User
Join Date
Nov 2004
Posts
1,428
Pat, Roac,

When I wrote
but I find the explanations confusing
I was referring to the very paragraph you (Pat) quoted, saying
what I think is a good description
This can only mean that you are way more intelligent than me. I'm saying this loud and clear, just in case someone out there was still in doubt.

The explanation of Roac is more to my level than that of BOL.
Anyway, I'll just have to play with it a bit before I'll be able to grok it.

Thank you guys.

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579

If you think about the table valued function in terms of the result set that it retourns then a CROSS APPLY on the function behaves like an INNER JOIN on the result set. Following the same line of reasoning, an OUTER APPLY behaves like a FULL OUTER JOIN on the result set.

Do I get at least a sip of beer this time ???

-PatP

10. Registered User
Join Date
Mar 2007
Location
Holmestrand, Norway
Posts
332
Originally Posted by Wim
When I wrote I was referring to the very paragraph you (Pat) quoted, saying This can only mean that you are way more intelligent than me. I'm saying this loud and clear, just in case someone out there was still in doubt.
I don't think so. The ability to understand technical documentation/text has nothing to do with intelligence, but has to be learned. As Microsoft Certified Trainer I've seen brilliant minds struggling with technical documentation.

11. Registered User
Join Date
Nov 2004
Posts
1,428
Originally Posted by Pat Phelan
Do I get at least a sip of beer this time ???
Definitely! You and Roac deserve a whole pint of it.

12. Registered User
Join Date
Nov 2004
Posts
1,428