Solved

Date formula for Birthday

Posted on 2011-03-07
28
284 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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
 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

679 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