July 11, 2003 at 4:30 am
How can i fing AGE in date field and to to round monte
---------
question 1
---------------
for example
the day of born
25/02/62 then what is the age ????
--------------------------------------
question 2
HOW can i round the age after
if the age is big then 41.9 then round it to 42
---------------
thnks
ilan
July 11, 2003 at 4:44 am
Hi midan1,
quote:
---------question 1
---------------
for example
the day of born
25/02/62 then what is the age ????
--------------------------------------
according to julian dates you get a good approximation when you subtract
now() - <your_date> and divide this by 365.2524. Then you have the number of days. That's what I have done. To calculate the month I guess above result by 12 or 365... /12
quote:
question 2HOW can i round the age after
if the age is big then 41.9 then round it to 42
---------------
round(<your_result,0) should be fine
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 11, 2003 at 4:51 am
This is the way I get age.
DECLARE @d datetime
SET @d = '7/12/1974'
select datediff(yyyy,cast(@d as datetime),getdate()) - (CASE WHEN dateadd(yyyy,datediff(yyyy,cast(@d as datetime),getdate()),cast(@d as datetime)) > GETDATE() THEN 1 ELSE 0 END)
And with SQL 2000 I can make a function out of the above for easy of reuse.
July 12, 2003 at 3:45 pm
ok thanks
------------
but how can i to create a VIEW
thet one field is the date of born
and a new field thet show the age
like this
---------------
"date" | "age"
25/02/62 | 41
23/02/61 | 42
--------------------
thnks ilan
July 12, 2003 at 4:44 pm
Then say your column name is birthdate do like so
select birthdate, datediff(yyyy,cast(birthdate as datetime),getdate()) - (CASE WHEN dateadd(yyyy,datediff(yyyy,cast(birthdate as datetime),getdate()),cast(birthdate as datetime)) > GETDATE() THEN 1 ELSE 0 END) as age FROM tblNameHere
This si why I state if you are using SQL 2000 it could be as simple as creating a funtion call udf_age with the code so you can do
Select birthdate, dbo.udf_age(birthdate) as age FROM tblNameHere
Makes it reusable and easier to deal with.
July 12, 2003 at 5:14 pm
OK it work 100% thnks
question 1
---------------------
but i wont to see in the age field
age like this
----------
"age"
40.6|
39.8|
22.6|
------------
question 2
HOW can i round the age after
if the age is big then 41.9 then round it to 42
so if the age is more then 9 month i won to round IT
------------------
thnks
agin
ilan
July 14, 2003 at 3:40 am
Hi ilan,
thinking about it a while and digging in some older code, what has not been taken into consideration yet, is what - I guess in english it's called - leap years. If you can live with this small inaccuracy Antares686 solution is great. In fact when you just show whole numbers no one will even notice.
To determine whether the current year is a leap year one can use this
IF ( YEAR ( @Today ) % 400 = 0 ) Or
( YEAR( @Today ) % 4 = 0 AND YEAR ( @Today ) % 100 <> 0 )
PRINT 'Schaltjahr - 366 Tage'
ELSE
PRINT 'Kein schaltjahr - 365.25.. Tage'
Another way to get the same as Antares686 is
SELECT year ( dateadd( dd, datediff ( dd , @birthday , @d ) ,
'01.01.1900 00:00' ) ) - 1900
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 14, 2003 at 4:53 am
quote:
Hi ilan,thinking about it a while and digging in some older code, what has not been taken into consideration yet, is what - I guess in english it's called - leap years. If you can live with this small inaccuracy Antares686 solution is great. In fact when you just show whole numbers no one will even notice.
To determine whether the current year is a leap year one can use this
IF ( YEAR ( @Today ) % 400 = 0 ) Or
( YEAR( @Today ) % 4 = 0 AND YEAR ( @Today ) % 100 <> 0 )
PRINT 'Schaltjahr - 366 Tage'
ELSE
PRINT 'Kein schaltjahr - 365.25.. Tage'
Another way to get the same as Antares686 is
SELECT year ( dateadd( dd, datediff ( dd , @birthday , @d ) ,
'01.01.1900 00:00' ) ) - 1900
Cheers,
Frank
Thanks for pointing that out. I am looking for the code I used to handle that right now. But the question Ilan I have is you say age is big the 41.9 round to 42 what do you cosider big. .5 and up or other?
July 14, 2003 at 9:36 am
Try this UDF:
-----------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_AgeFromDOB]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_AgeFromDOB]
GO
CREATE FUNCTION [dbo].[udf_AgeFromDOB]
(
@DOB as datetime,
@Now as datetime
)
returns int
as
begin
declare @ret int
-- declare @Now datetime
-- Exec @now = dbo.dt_FetchCurrentDate null
-- set @Now = '07/09/2003'
set @ret = 0
set @ret = datediff(mm,@DOB, @now)
return @ret
end
-- =============================================
-- Example to execute function
-- =============================================
-- SELECT *
-- FROM <owner, , dbo>.[dbo].[udf_AgeFromDOB]
-- ('10/12/1923', Getdate())
-- GO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------------------
Call it as follows:
SELECT *
FROM <owner, , dbo>.[dbo].[udf_AgeFromDOB]
('10/12/1923', Getdate())
GO
I wrote and tested this under SQL server 2k
July 14, 2003 at 10:31 am
wow thnks a lot
the best Forum in the net !!!!
ilan
July 14, 2003 at 12:15 pm
Try this as well
http://qa.sqlservercentral.com/scripts/contributions/242.asp
G.R. Preethiviraj Kulasingham
Chief Technology Officer.
Softlogic Information Systems Limited,
14 De Fonseka Place,
Colombo 05.
Sri Lanka.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
July 24, 2003 at 10:17 am
This is how I'd do it:
declare @bd datetime
select @bd = '1976-12-2'
select case when month(@bd) > month(getdate()) then datediff(year, @bd, getdate())-1
when month(@bd) = month(getdate()) and day(@bd) < day(getdate()) then datediff(year, @bd, getdate())-1
when month(@bd) = month(getdate()) and day(@bd) >= day(getdate()) then datediff(year, @bd, getdate())
when month(@bd) < month(getdate()) then datediff(year, @bd, getdate())
end
Maybe I'm missing the point, but why would you need to know about leap years?
-Ken
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply