Age @ TOS help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Age @ TOS help

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-02-04 : 17:16:57

I need help with the following field:

'Age @ TOS' = DATEDIFF (YY, patientprofile.birthdate, patientvisitprocs.dateofservicefrom),

PatientProfile.Birthdate - 01/01/2001
PatientVisitProcs.dateofservicefrom = 07/01/2001

In my report, I get a '0' as my Age @ TOS. What I would like to have is 7 months vs the '0' it is reporting.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-04 : 17:22:52

If you want the difference in months, why did you ask for the difference in years in the DATEDIFF function?

You should look at the functions below to see if they help with what you want to do.

This function returns age in format YYYY MM DD.
Age Function F_AGE_YYYY_MM_DD:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
This function returns age in years.
Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

CODO ERGO SUM

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-02-04 : 17:25:03

99% of the patients are over 1 year. It was the one patient under that messed this up for me. I will take a look at these functions.

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-02-04 : 17:26:46

Michael,

Is it possible to do this without using a function?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-04 : 17:29:46

quote:
Originally posted by JeffS23

Michael,

Is it possible to do this without using a function?

Yes, but probably not worth the effort.

CODO ERGO SUM

pdreyer
Starting Member

6 Posts

Posted - 2008-02-14 : 08:16:12

Note: There is a bug in the F_AGE_YYYY_MM_DD function

Modify the below example to fit your needs


select fromdt ,todt ,duration= 
case when datepart(dd,todt)<datepart(dd,fromdt) then 
   convert(varchar(4),(datediff(mm,fromdt,todt)-1)/12)+' Years ' 
  +convert(varchar(2),(datediff(mm,fromdt,todt)-1)%12)+' Months ' 
  +convert(varchar(2),datediff(dd,dateadd(mm, 
           (datediff(mm,fromdt,todt)-1),fromdt),todt))+' Days' 
else 
   convert(varchar(4),datediff(mm,fromdt,todt)/12) +' Years ' 
  +convert(varchar(2),datediff(mm,fromdt,todt)%12) +' Months ' 
  +convert(varchar(2),datediff(dd,dateadd(mm, 
         (datediff(mm,fromdt,todt)),fromdt),todt)) +' Days' 
end 
from 
 (select convert(datetime,'20000229') fromdt
       ,dateadd(dd,number,'20000229') todt 
  from master..spt_values 
  where type='P' 
 ) view1 
order by 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources