need hep on update query, -datetime
-
I need a query to update a table to change the values in the AGe column based on values in the DateOfBirth column.
so current date minus the date of birth is what i want to set the age column too.
can someone help
something like:
update clients
(getdate() - DateofBrith)
note the datof birthcolume is in datetime format.
thanks!!!
-
update clients
set age = datediff(yy, DateOfBirthColumn,getdate())
Instead of persisting the age in the db, why dont you just use the calculation in the presentation layer?
anyways that will work
-
Check this out. It is a simple call to the datediff function (see BOL for more info):
declare @date1 datetime, @date2 datetime
set @date1 = convert(datetime, '8/29/1959')
set @date2 = convert(datetime, '6/2/1998')
select datediff(yyyy, @date1, getdate())
select datediff(yyyy, @date2, getdate())
Hope this helps.
Lynn
-
As someone else suggested, why make the AGE persist? I'm thinking it would be much easier to add a calculated column to the table so that you don't have to keep writing the same code over and over
but you will still have access to current ages, all the time. 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
i get your suggestion and thank u for it in the presentation layer or making another field. But I still want to figure how to do this.
ok so using veteran's code, what is the yy for (i know year year but...)
what that work for columna that look this this:
update clients
set age = datediff(yy,DateofBirth,getate())
age DateofBirth
59 1964-01-01 00:00:00
-
I'm not sure what your question is. At this point you have a valid update statement:
update clients
set age = datediff(yy, DateofBirth, GetDate())
...
-
Adam, I think part of the problem is, the formula doesn't work as everyone expects
because DATEDIFF only looks at boundaries crossed of the date part which is a year, in this case... If a person's birthday is 11/15/1952, then as of today 08/30/2005, that person's age is only 52. They won't be 53 until November 15th but the following returns 53.
SELECT DATEDIFF(yy,'11/15/1952',GETDATE())
In fact, the above erroneously returns the incorrect age of 53 for ANY getdate value in the year 2005 prior to the actual date of birth.
Here's a possible work around... it actually takes advantage of the whole year error of the above code and then... if today's month and day (mm/dd) is less than the DOB, it subtracts 1. Seems to work correctly including leap years.
Here's the test code I used... (the math operators start on a separate line just for readability here). Just change the date for @DOB to see what I mean. Use yesterday's date, today's date, and tomorrow's date (try that in the above original formula, as well!)
DECLARE @DOB DATETIME
SET @DOB = '11/15/1952'
SELECT DATEDIFF(yy,@DOB,GETDATE())
- CASE
WHEN CONVERT(CHAR(5),GETDATE(),101)
< CONVERT(CHAR(5),@DOB,101)
THEN 1
ELSE 0
END AS AGE
This would also produce the correct answer, might be slightly slower because of having 1 additional function, and might be slightly easier to understand...
DECLARE @DOB DATETIME
SET @DOB = '11/15/1952'
SELECT YEAR(GETDATE())-YEAR(@DOB)
- CASE
WHEN CONVERT(CHAR(5),GETDATE(),101)
< CONVERT(CHAR(5),@DOB,101)
THEN 1
ELSE 0
END AS AGE
Just a note... The conversion of the dates, in the case statement, uses the mm/dd/yyyy format but since only 5 characters are allowed (CHAR(5)) in the conversion, it's like taking the left 5 characters without actually added LEFT to the code. That produces the mm/dd for comparison. Since mm and dd are zero filled and the month comes before the day, it does the comparison correctly.
Hope that clears things up a bit... I haven't tried a similar algorithym as a calculated column but you can probably guess that's what I'll try next.
I'm also thinking that some people are going to have to scramble to correct their aging algorithym's
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's what I use when determining ages
-- 2003-03-11 / Kenneth Wilhelmsson
-- Determining current age notes.
-- To be able to determine current age based on DOB date, you first must
-- decide which way to round if the birthday is today.
-- If the new year should be counted, round up (ceiling),
-- if the new year should be counted tomorrow, round down (floor)
-- current age rounded down
declare @dob char(8)
set @dob = '19620830'
select floor(datediff(day, @dob, getdate()) / 365.25)
go
-- rounded up
declare @dob char(8)
set @dob = '19620830'
select ceiling(datediff(day, @dob, getdate()) / 365.25)
go
-- sometimes DOB has no century - this query solves this.
-- rounded down
declare @dob char(6)
set @dob = '420228'
select case sign ( floor(datediff(day, @dob, getdate()) / 365.25) )
when -1
then floor(datediff(day, @dob, getdate()) / 365.25) + 100
else floor(datediff(day, @dob, getdate()) / 365.25)
end as 'age'
-- rounded up
declare @dob char(6)
set @dob = '420228'
select case sign ( ceiling(datediff(day, @dob, getdate()) / 365.25) )
when -1
then ceiling(datediff(day, @dob, getdate()) / 365.25) + 100
else ceiling(datediff(day, @dob, getdate()) / 365.25)
end as 'age'
-- some northwind examples
use northwind
go
-- 1) How old are all employees today
select Firstname + ' ' + Lastname +
' (' +
cast(floor(datediff(day, convert(char(8), BirthDate, 112), getdate()) / 365.25) as char(3)) +
' years old)' as event_name
, BirthDate
from employees
-- 2) List all with current age that have a birthday in the current month
select Firstname + ' ' + Lastname +
' (' +
cast(floor(datediff(day, convert(char(8), BirthDate, 112), getdate()) / 365.25) as char(3)) +
' years old)' as event_name
, BirthDate
from employees
where datepart(month, BirthDate) = datepart(month, getdate())
/******* end **********/
/Kenneth
-
Kenneth, what about leap years ?
-
Yes? What about them?
Leap years is the reason to divide by 365.25 instead of 365...
As a sidenote, I can't take credit for the algorithm, I read it in some article that I've forgot which. But the reason for using days as unit in datediff instead of year, had something to do with how datediff(year) was implemented.
So far, I've found that this way seems to work.
/Kenneth
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply