Link to home
Start Free TrialLog in
Avatar of srodgers45
srodgers45

asked on

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
Avatar of Amgad_Consulting_Co
Amgad_Consulting_Co
Flag of Egypt image

select *from <youtable>
where datediff(day, convert(char(8),birthdate,112) ,convert(char(8),getdate(),112) ) >=15
Avatar of UnifiedIS
UnifiedIS

it should be datediff (yy... for year
and ...<= 15
yes, you are right, sorry for the miss
Avatar of Member_2_861731
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.
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

Open in new window

Avatar of srodgers45

ASKER

It almost works. I am getting an error midway thru the query. User generated image
This is the Table I am using - the date fields ar varchar not datetime, does that make a difference? User generated image
This is how the query is set up
 User generated image
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
You could do something like this:
select dbo.GetAgeAtDate(CONVERT(DATETIME,dob,102),CONVERT(DATETIME,tdate,102))

That should give you the date difference.
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
Where you're calling the function in your where clause:

WHERE dbo.GetAgeAtDate(CONVERT(DATETIME,dob,102),CONVERT(DATETIME,tdate,102)) <= 15
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 User generated image
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
It returns allot of "Nulls" Can those be removed from the equation? I tried to filter out the "Null" stil returned the same message
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.
It returned data, the Nulls were in the dob columns.
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
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 User generated image User generated image
Also another strange thing, when i try to filter by DOB less than 1996-01-01, the query returns all dob's ? User generated image
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!
are you sure that your date field is stored in " yyyy-mm-dd " format?
Here is what the properties show for both date tables

 User generated image
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.
This was the result for both columns:
Thanks for your patience

 User generated image
have you check all records, and the dates are correct ?
Yes, and in the format shown above.
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.
They seem to all match. No Null Values User generated image User generated image
ASKER CERTIFIED SOLUTION
Avatar of Member_2_861731
Member_2_861731
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial