date math - 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 2000 Forums

Analysis Services (2000)

date math

Author

Topic

dmgilbert
Starting Member

10 Posts

Posted - 2004-04-01 : 19:11:28

I am trying to create a calculated member age. I have the measure birth_date (mm/dd/yyyy). I am trying date()-[measure].[birth-date]but getting a syntax error. Any help?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-01 : 23:00:15

Well, assuming that the dash (-) in the second phrase is just a typo and you meant the underscore (_) in the first part, the next thing I'd look at is date(). I believe you want getdate() instead.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

dmgilbert
Starting Member

10 Posts

Posted - 2004-04-02 : 10:16:22

Yes, that was a syntax error in the second sentence. However, Getdate() is not recognized. Any other suggestions?

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-04-02 : 10:27:47

HTH..
Declare @birthdate datetime
select @birthdate = '1978-04-20 00:00:00'
Select 'AGE' = datediff(yy,@birthdate, getdate())

- Sekar

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-04-02 : 10:32:27

Calculating age is a little tricker than that Sekar. Datediff only returns the difference between the years of the two dates, it takes no account of month and day of the month to determine if the individual has had their birthday yet this year.

Raymond

dmgilbert
Starting Member

10 Posts

Posted - 2004-04-02 : 11:25:12

Thanks everyone for your suggestions, and pointing me in the right direction. The function that finally works is: datediff("yyyy",[measures].[birth_date],date()). Raymond is correct, that it does not take into account if a birthday has occurred in this year, so I am going to try and figure that one out.
Appreciate all the help!

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-02 : 12:06:21

Here is a way modifying Brett's a little:

SELECT	FLOOR(DATEDIFF(dd,[measures].[birthdate],GETDATE())/365.25) As Years

I tested some scenarios and they mostly held, but leap day birthdays could still be interesting, it is handled by default with 3/1 being the triggering date.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-04-05 : 04:01:17

To expand on Sekar's answer, what I had in mind was

Declare @birthdate datetime
select @birthdate = '1978-04-02 00:00:00'
Select 'AGE' = datediff(yy,@birthdate, getdate()) - CASE WHEN DATEPART(dy, @birthdate) > DATEPART(dy, GETDATE()) THEN 1 ELSE 0 END

Raymond

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-05 : 09:01:32

Nice one, Raymond -- i always forget about the DY option for the DATEPART function ....

I'd have to say that's probably the best way to go about solving this problem.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources