• Status: Solved
• Priority: Medium
• Security: Public
• Views: 294

# Date formula for Birthday

I have a view on SQL 2005 that I need to show data for patients that are 15 or younger. I have a date of service and date of birth for the persons. How do I show just the persons that meet that criteria with a date calculation.

Date Of Service - DOB = 15 years and younger
0
srodgers45
• 11
• 7
• 6
• +2
1 Solution

Commented:
select *from <youtable>
where datediff(day, convert(char(8),birthdate,112) ,convert(char(8),getdate(),112) ) >=15
0

Commented:
it should be datediff (yy... for year
and ...<= 15
0

Commented:
yes, you are right, sorry for the miss
0

Commented:
If you want "exact" years that's not gonna work and you'll need to evaluate months and days, because that will only give you the difference of the years.

Just my 0.02.
0

Commented:
this udf will return the age at an exact date. to use it, you'd do "select * from table where dbo.GetAgeAtDate(table.DOB, getdate()) <= 15

``````create function [dbo].[GetAgeAtDate]
(
--<parameters>
@Birthdate datetime,    -- <param required="yes" description="Birth day"/>
@Date datetime          -- <param required="yes" description="Datetime to get age in years for"/>
--</parameters>
)
returns int
as
begin
declare @Age int

if (datepart(mm,@Date) < datepart(mm,@Birthdate))
or ((datepart(mm,@Date) = datepart(mm,@Birthdate)) and (datepart(dd,@Date) < datepart(dd,@Birthdate)))
begin
select @Age = datepart(yyyy,@Date) - datepart(yyyy,@Birthdate) - 1
end
else
begin
select @Age = datepart(yyyy,@Date) - datepart(yyyy,@Birthdate)
end

return @Age
end
``````
0

Author Commented:
It almost works. I am getting an error midway thru the query.
This is the Table I am using - the date fields ar varchar not datetime, does that make a difference?
This is how the query is set up

0

Commented:
that is because the date is stored as text, can you please try this:

select *from <youtable>
where datediff(day,  birthdate ,convert(char(8),getdate(),120) ) <=15

if doesn't work, please check other convert date formats here http://msdn.microsoft.com/en-us/library/ms187928.aspx
0

Commented:
You could do something like this:
select dbo.GetAgeAtDate(CONVERT(DATETIME,dob,102),CONVERT(DATETIME,tdate,102))

That should give you the date difference.
0

Author Commented:
How and where would I insert that? in the view, I created the GetAgeAtDate function which works other than the error above for the conversion.

I tried both suggestions, just not sure if i am using them correctly to get the results

I think its close to being complete.

Thanks for the help
0

Commented:
Where you're calling the function in your where clause:

WHERE dbo.GetAgeAtDate(CONVERT(DATETIME,dob,102),CONVERT(DATETIME,tdate,102)) <= 15
0

Author Commented:
I did that, came up with this error. the query is returning records but stops midway through. What if there where a table that should the actual age and I where to filter for ages less than 15 based on that? would that work? I am definitely not the expert
0

Commented:
see if this returns any records. if it does, you have some data in your datecolumn that isn't a correctly formatted date

select * from <tbl_or_view> where isdate(dob) = 0
0

Author Commented:
It returns allot of "Nulls" Can those be removed from the equation? I tried to filter out the "Null" stil returned the same message
0

Commented:
If you're getting that message it means some of your data isn't a valid date... The query posted by derek should help identify them.
0

Author Commented:
It returned data, the Nulls were in the dob columns.
0

Commented:
Try to filter out nulls

in sql: "where dob is not null and tdate is not null and ..............................."

also can you how check 2 fields, it looks like a problem in formating, a work around my be removing the '-' from the date so it will be in iso format

i.e. dbo.GetAgeAtDate( REPLACE(dob,'-',''), REPLACE(tdate,'-','')) <= 15
0

Author Commented:
Tried both Suggestions, still coming up with the error. Any thoughts or help on using a formula to add a new column to the view that would subtract the tdate from the dob to give me the age in in years. Then I could use that to filter for ages less than 15? Is that possible?

Thanks for sticking with me on this.

These are the errors
0

Author Commented:
Also another strange thing, when i try to filter by DOB less than 1996-01-01, the query returns all dob's ?
0

Commented:
What you're doing in your last query is not a date comparison, but a char comparison because both of the operators (dob and your filter) are chars not dates, so SQL is comparing them like strings.

You can try this:
Select all the date values and be sure they return the same value once cast into datetime. Something like SELECT CONVERT(DATETIME,dob,102) FROM myTable

Do this on each of your date columns to see if the values are the same.

Let us know how it goes!
0

Commented:
are you sure that your date field is stored in " yyyy-mm-dd " format?
0

Author Commented:
Here is what the properties show for both date tables

0

Commented:
You need to check the data stored in the dob and tdate columns. You should check if the dates are valid and represent the correct date (for SQL). You can do this with the code I posted above.
0

Author Commented:
This was the result for both columns:

0

Commented:
have you check all records, and the dates are correct ?
0

Author Commented:
Yes, and in the format shown above.
0

Commented:
So you're certain that the dates match when you convert them using the convert function? Because if they don't match something in the format might be the problem.

I see some dates in your last screen print that I don't see in some of the previous ones.

With a query like:

SELECT dob, CONVERT(DATETIME,dob,102) FROM dbo.v_core_indicator_information

you should get the exact same dates on both columns. Then try it on the tdate column, you should get the exact same values too... If not, then there's some problem with your data and/or formatting of the values.

Remember to filter out NULL values.
0

Author Commented:
They seem to all match. No Null Values
0

Commented:
Now you can try to use the provided function:

SELECT dbo.GetAgeAtDate(CONVERT(DATETIME,dob,102),CONVERT(DATETIME,tdate,102))
FROM dbo.V_Core_Indicator_Information

Do you get the error?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.