sql server 2000 date's arithmetics 1

[pssheba]

Hi !
i'd like to calculate clients' ages whereby using today's date minus "birthdate" colomnn. Code should look like:

select
today()-mytable.birthdate "Age"
from
mytable
where
today()-mytable.birthdate < 80

evidently "today()" is wrong. Could any one tell me which function shows the "now" date ?
The correct syntax for the above code ?
Thanks !

 

[mutley1]

getdate ()

Cheers,

M.

 

[pssheba]

Hi mutley1 and thank you !
when i substract a date from another date i get a third date.
I like to show an age. Something like:
31/12/2000-31/12/1920 = 80
by substracting someone's birthdate from today's date i want to know his age. Could you help me with that ?

 

[mutley1]

I've done this with a column called birthday (sorry, misread it) so just change the code....

select person, datediff(yy, birthday, getdate()) -
	(case when (datepart(m, birthday) > datepart(m, getdate())) or
			(datepart(m, birthday) = datepart(m, getdate()) And
				datepart(d, birthday) > datepart(d, getdate()))
			then 1
			else 0
	end) as Age1
from mytable.birthday

Cheers,

M.

 

[mutley1]

Hey pssheba,

Here's a revised version (sure one of the Guru's can improve on it for you, but it sould do the trick as I'm no expert coder!)

Changed the field so it's birthdate and if you just replace the "80" value with whatever age you are looking for (obviously it's set to less than but you can also do the greater than as well) it should bring back your results. If you want other fields as well (haven't seen your table structure) then add them in in the select. I called the field person for ease of use.

select person, datediff(yy, birthdate, getdate()) -
	(case when (datepart(m, birthdate) > datepart(m, getdate())) or
			(datepart(m, birthdate) = datepart(m, getdate()) And
				datepart(d, birthdate) > datepart(d, getdate()))
			then 1
			else 0
	end) as Age
into tempbday
from birthdate
select * from tempbday
where age < 80
drop table tempbday

Cheers,

M.

 

[pssheba]

Hi mutley1 !
I couldnt check the code but i'll do it tommorow and if i have troubles i'll tell you about it. But by now i can only thank you !

 

[SQLBill]

Check the FAQs for this forum. There is a section for DATETIME Tips & Tricks.

-SQLBill

Posting advice: FAQ481-4875

 

[pssheba]

I 'll do that, i need to know a lot about dates

 

[mutley1]

Thanks Bill - it'll give me something to swot up on as well!!

HNY...