December 22, 2015 at 11:07 am
Hi There,
I am trying to find list of members within the ages of 13-17 in a particular dept.
I query the results and I have the list with Date of Birth, now I would like to convert the DOB to age using DateDiff function.
Can you suggest how to go about it?
Thanks in advance.
Regards,
SQLisAwe5oMe.
December 22, 2015 at 11:25 am
It's not too difficult...
SELECT DATEDIFF (YEAR, '1947-11-22 00:00:00.000', GETDATE()) AS Age
Checking out the MSDN page would have given you the answer.
https://msdn.microsoft.com/en-CA/library/ms189794.aspx
December 22, 2015 at 11:30 am
another version, which takes into consideration if the birthday has passed or not:
DECLARE @DOB datetime
SET @DOB='1962-12-11'
SELECT CASE
WHEN DATEPART(DY,GETDATE()) >= DATEPART(DY,@DOB)
THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1
END
Lowell
December 22, 2015 at 11:39 am
Lowell (12/22/2015)
another version, which takes into consideration if the birthday has passed or not:
DECLARE @DOB datetime
SET @DOB='1962-12-11'
SELECT CASE
WHEN DATEPART(DY,GETDATE()) >= DATEPART(DY,@DOB)
THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1
END
Good catch...I always forget about that. :hehe:
This should work as well for this purpose.
DECLARE @dob TABLE (birthdate DATETIME)
INSERT INTO @dob
VALUES ('1947-12-21 00:00:00.000'), ('1947-12-22 00:00:00.000'), ('1947-12-23 00:00:00.000')
SELECT DATEDIFF(hour,birthdate,GETDATE())/8766 AS AGE FROM @dob
December 22, 2015 at 12:15 pm
The really tricky part of this is deciding how you want to handle Feb 29 birthdays: in non-leap years, is that birthday Feb 28 or Mar 1? In certain cases, such as health insurance, there might even be laws that require you to use a specific one of those methods.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 22, 2015 at 12:25 pm
ScottPletcher (12/22/2015)
The really tricky part of this is deciding how you want to handle Feb 29 birthdays: in non-leap years, is that birthday Feb 28 or Mar 1? In certain cases, such as health insurance, there might even be laws that require you to use a specific one of those methods.
Pffft...those leap year babies think they're so special. 😀
I jest of course, that is a really good point you make.
December 22, 2015 at 12:58 pm
How do I add this into my query ?
SELECT "Name"."ID", "Name"."LAST_NAME", "Name"."FIRST_NAME", "Name"."EMAIL", "Activity"."ACTIVITY_TYPE", "Activity"."PRODUCT_CODE", "Activity"."TRANSACTION_DATE", "Name"."BIRTH_DATE"
FROM "IMIS"."dbo"."Activity" "Activity" INNER JOIN "IMIS"."dbo"."Name" "Name" ON "Activity"."ID"="Name"."ID"
WHERE ("Activity"."ACTIVITY_TYPE"='MEETING' OR "Activity"."ACTIVITY_TYPE"='ORDER' OR "Activity"."ACTIVITY_TYPE"='SALES')
so, I will have a column called "Age" next to my "Birth_Date" column.
Regards,
SQLisAwe5oMe.
December 22, 2015 at 1:07 pm
SELECT "Name"."ID", "Name"."LAST_NAME", "Name"."FIRST_NAME", "Name"."EMAIL", "Activity"."ACTIVITY_TYPE", "Activity"."PRODUCT_CODE", "Activity"."TRANSACTION_DATE", "Name"."BIRTH_DATE", DATEDIFF(YEAR, "Name"."BIRTH_DATE", GETDATE()) - CASE WHEN
CONVERT(char(5), GETDATE(), 101) < CONVERT(char(5), "Name"."BIRTH_DATE", 101) THEN 1 ELSE 0 END AS "AGE"
FROM "IMIS"."dbo"."Activity" "Activity"
INNER JOIN "IMIS"."dbo"."Name" "Name" ON "Activity"."ID"="Name"."ID"
WHERE ("Activity"."ACTIVITY_TYPE"='MEETING' OR "Activity"."ACTIVITY_TYPE"='ORDER' OR "Activity"."ACTIVITY_TYPE"='SALES')
Edit: Calc corrected.
Btw, note that DATEPART(DAYOFYEAR,...) is also not accurate because of leap years :angry:.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 23, 2015 at 7:52 am
Thank you guys, appreciate it.
I just want to confirm one more thing, if I need to avoid duplicates names, select distinct would work right, as shown below?
SELECT distinct "Name"."ID", "Name"."LAST_NAME", "Name"."FIRST_NAME", "Name"."EMAIL", "Activity"."ACTIVITY_TYPE", "Activity"."PRODUCT_CODE", "Activity"."TRANSACTION_DATE", "Name"."BIRTH_DATE", DATEDIFF(YEAR, "Name"."BIRTH_DATE", GETDATE()) - CASE WHEN
CONVERT(char(5), GETDATE(), 101) < CONVERT(char(5), "Name"."BIRTH_DATE", 101) THEN 1 ELSE 0 END AS "AGE"
FROM "IMIS"."dbo"."Activity" "Activity"
INNER JOIN "IMIS"."dbo"."Name" "Name" ON "Activity"."ID"="Name"."ID"
WHERE ("Activity"."ACTIVITY_TYPE"='MEETING' OR "Activity"."ACTIVITY_TYPE"='ORDER' OR "Activity"."ACTIVITY_TYPE"='SALES')
Regards,
SQLisAwe5oMe.
December 23, 2015 at 7:54 am
You didn't ask about this, but you can also clean up your WHERE statement a little bit by using 'IN'.
For example:
Rather than using this -
WHERE ("Activity"."ACTIVITY_TYPE"='MEETING' OR "Activity"."ACTIVITY_TYPE"='ORDER' OR "Activity"."ACTIVITY_TYPE"='SALES')
I would use this -
WHERE Activity.ACTIVITY_TYPE IN ('MEETING', 'ORDER', 'SALES')
The two WHERE clauses above would, I believe, return the result set, but you save yourself some typing the second way.
December 23, 2015 at 9:21 am
ScottPletcher (12/22/2015)
The really tricky part of this is deciding how you want to handle Feb 29 birthdays: in non-leap years, is that birthday Feb 28 or Mar 1? In certain cases, such as health insurance, there might even be laws that require you to use a specific one of those methods.
Good point, and it applies to anyone born in a leap year on/after February 29, not just those born on February 29. For DOB of December 23, for example, years 2014 and 2012 return different values:
SELECT
DATEPART(DayOfYear, '2014-12-23'),--Non leap year, returns 357
DATEPART(DayOfYear, '2012-12-23');--Leap year, returns 358
The practical upshot of this? If today is your birthday when you run the script, Lowell's code will return an age that is one year less if you were born after 2/28 in a leap year. For the example above with a leap year birthday of 2012-12-23 (and executed today on 2015-12-23), the person will return with an age of 2, not 3. You'd have to wait to run it tomorrow on 2015-12-24 to return an age of 3.
I have an old and somewhat clunky workaround for this that I hesitate to post b/c it is so clunky. I don't do a lot of age calculations on large data sets, so it's met my needs, but post back if you want it.
Rich
December 23, 2015 at 9:24 am
Here is an article that may help: http://www.sqlservercentral.com/articles/T-SQL/63351/
December 23, 2015 at 10:25 am
ALTER VIEW dbo.[Vw_GetCurrentDate]
WITH SCHEMABINDING
/*
* A view to return one row, with one column, the current
* date/time from the built-in function GETDATE(). This
* view allows a UDF to bypass the restriction on access to
* the non-deterministic getdate() function.
*
* Attribution: Based on a newsgroup posting in by Mikhail
* Berlyant in microsoft.public.sqlserver.programming
*
* Example:
DECLARE @dtVar datetime
select @dtVAr = [GetDate] from Vw_GETDATE
**********************************************************/
AS
SELECT convert(datetime,convert(char(10),getdate(),101)) as [GetDate]
GO
ALTER FUNCTION [dbo].[udf_GetAge] (
@DateOfBirth datetime -- Day person was born
, @AsOfDate datetime = NULL -- As of date. NULL for today
) RETURNS int -- Age of the person in years
/* Returns the age of a person, in years, for a given date
* of birth, as of a given date. If no @AsOfDate is supplied then
* today is used.
*
* Example:
SELECT [SYSDBA].[udf_GetAge] ('2000-06-01', default)
*
****************************************************************/
AS BEGIN
DECLARE @Age int --Age in years.
-- Handle NULL @AsOfDate
IF @AsOfDATE IS NULL SELECT @AsOfDate = [GetDate] FROM dbo.Vw_GetCurrentDate
SET @Age = YEAR(@AsOfDATE) - YEAR(@DateOfBirth)
IF MONTH(@DateOfBirth) > MONTH(@AsOfDATE) SET @Age = @Age - 1
IF MONTH(@DateOfBirth) = MONTH(@AsOfDATE)
BEGIN
IF DAY(@DateOfBirth) > DAY(@AsOfDATE)
BEGIN
SET @Age = @Age - 1
END
END
RETURN @Age
END
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply