Solved

Date formula for Birthday

Posted on 2011-03-07
28
281 Views
Last Modified: 2012-05-11
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
Comment
Question by:srodgers45
  • 11
  • 7
  • 6
  • +2
28 Comments
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 35059981
select *from <youtable>
where datediff(day, convert(char(8),birthdate,112) ,convert(char(8),getdate(),112) ) >=15
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 35060046
it should be datediff (yy... for year
and ...<= 15
0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 35060061
yes, you are right, sorry for the miss
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 35060184
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
 
LVL 15

Expert Comment

by:derekkromm
ID: 35060200
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

0
 

Author Comment

by:srodgers45
ID: 35063799
It almost works. I am getting an error midway thru the query. Error
This is the Table I am using - the date fields ar varchar not datetime, does that make a difference? Table used
This is how the query is set up
 Query
0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 35064993
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
 
LVL 13

Expert Comment

by:LIONKING
ID: 35069047
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 Comment

by:srodgers45
ID: 35077398
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
 
LVL 15

Expert Comment

by:derekkromm
ID: 35077529
Where you're calling the function in your where clause:

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

Author Comment

by:srodgers45
ID: 35077853
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 Error Message
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35078174
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 Comment

by:srodgers45
ID: 35078280
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
 
LVL 13

Expert Comment

by:LIONKING
ID: 35078889
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:srodgers45
ID: 35078907
It returned data, the Nulls were in the dob columns.
0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 35080289
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 Comment

by:srodgers45
ID: 35090370
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 Error Message 3 Error Message 4
0
 

Author Comment

by:srodgers45
ID: 35090545
Also another strange thing, when i try to filter by DOB less than 1996-01-01, the query returns all dob's ? DOB
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 35090969
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
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 35092893
are you sure that your date field is stored in " yyyy-mm-dd " format?
0
 

Author Comment

by:srodgers45
ID: 35096787
Here is what the properties show for both date tables

 Format
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 35098168
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 Comment

by:srodgers45
ID: 35098477
This was the result for both columns:
Thanks for your patience

 Query Result for Dates
0
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 35108356
have you check all records, and the dates are correct ?
0
 

Author Comment

by:srodgers45
ID: 35110592
Yes, and in the format shown above.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 35111288
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 Comment

by:srodgers45
ID: 35111824
They seem to all match. No Null Values DOB TDate
0
 
LVL 13

Accepted Solution

by:
LIONKING earned 500 total points
ID: 35112453
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now