Hi,
Is there a way to calculate the Default Value of a column with other data
columns? Using "date_of_birth" column and "screen_date" columns, I would
like to calculate the "age" automatically with the Default Value definition.
Please let me know if it's possible.
YC> Is there a way to calculate the Default Value of a column with other data
> columns? Using "date_of_birth" column and "screen_date" columns, I would
> like to calculate the "age" automatically with the Default Value
> definition.
Why would you need to store this data? If you have 2 in one column and 2 in
another column, do you really need to store 4 in a "sum" column, when you
can do this in a view or procedure?
Anyway, I think you want to use a computed column, not a default value.
E.g.:
CREATE TABLE dbo.MyTable
(
db SMALLDATETIME,
sd SMALLDATETIME,
age_in_days AS CONVERT(INT, DATEDIFF(DAY, db, sd))
)
GO
SET NOCOUNT ON
INSERT dbo.MyTable(db, sd) SELECT '20050101', GETDATE())
INSERT dbo.MyTable(db, sd) SELECT '20050501', '20050505')
SELECT * FROM dbo.MyTable
GO
DROP TABLE dbo.MyTable
GO|||Hi
It is not clear why you would want to store the age in this way. Current Age
would be SELECT DATEDIFF(yy,dob, getdate()) which could be added to an
INSTEAD OF trigger. If you want to have a more up-to-date age you can use
the function in a view e.g.
CREATE VIEW vw_Users AS
SELECT Name, dob, DATEDIFF(yy,dob, getdate()) AS Age
FROM tbl_users
SELECT Name, dob, age
FROM vw_Users
John
"Asp Psa" wrote:
> Hi,
> Is there a way to calculate the Default Value of a column with other data
> columns? Using "date_of_birth" column and "screen_date" columns, I would
> like to calculate the "age" automatically with the Default Value definitio
n.
> Please let me know if it's possible.
> YC
>
>|||John's formula does not break on the birthday. It will tell you how old
someone will be at the end of a year.
Aaron's formula can be tweaked to provide a person's age as of today. It
can be put into a computed column as Aaron has suggested or in a user-define
d
function or stored procedure.
CONVERT(INT, DATEDIFF(DAY, dob, getdate()) / 365.25) as age
Personlly, I would not store the age in a table because it's subject to
change each day. Or, if you need what year it is for the person (John's
formula) then you have to refresh the table data once per year.
Hope that helps,
Joe
"John Bell" wrote:
> Hi
> It is not clear why you would want to store the age in this way. Current A
ge
> would be SELECT DATEDIFF(yy,dob, getdate()) which could be added to an
> INSTEAD OF trigger. If you want to have a more up-to-date age you can use
> the function in a view e.g.
> CREATE VIEW vw_Users AS
> SELECT Name, dob, DATEDIFF(yy,dob, getdate()) AS Age
> FROM tbl_users
> SELECT Name, dob, age
> FROM vw_Users
> John
> "Asp Psa" wrote:
>|||> Why would you need to store this data?
Aaron,
sometimes we do that to speed queries up.
For instance, if there are columns order_date and shipment_date,
turnaround (days between ordering and shipment) and shipment_month can
be calculated no problem. But if there are many frequently running
queries involving these expressions, such as
select ...
where turnaround>10
select sum(amount), shipment_month, turnaround
from orders
group by shipment_month, turnaround
it really helps to create an index on (turnaround, shipment_month,
amount)
Makes sense?|||> Makes sense?
Yes, of course. I was asking if the OP really needed to do it, I don't
recall saying, "don't do that, it's stupid and doesn't make sense."|||I agree that this is not the way to go. It would be best to calculate
it in a view. BTW, sometimes age is static. For example a patient's
age is that at the time of admission. If you really need a separate
field for this, you could create a simple trigger.|||Joe
As people are born on different days, you will need to refresh it every day
(just in case!)!
John
"Joe from WI" wrote:
> John's formula does not break on the birthday. It will tell you how old
> someone will be at the end of a year.
> Aaron's formula can be tweaked to provide a person's age as of today. It
> can be put into a computed column as Aaron has suggested or in a user-defi
ned
> function or stored procedure.
> CONVERT(INT, DATEDIFF(DAY, dob, getdate()) / 365.25) as age
> Personlly, I would not store the age in a table because it's subject to
> change each day. Or, if you need what year it is for the person (John's
> formula) then you have to refresh the table data once per year.
> Hope that helps,
> Joe
> "John Bell" wrote:
>|||Age changes from day to day, so put the calculation in a VIEW. Do not
store computed data.|||I might have misread these posts.
A computed column is persisted only when required, for instance at the time
of use on the select clause.
Its not calculated and stored, unless its value is deterministic in which
case you would index and only then is the value persisted.
Its a lot more efficient and easier to do these age things based on timenow
using a computed column rather than a view.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C1B2D6CE-5BE3-46A3-AE8A-72E95161011D@.microsoft.com...
> Joe
> As people are born on different days, you will need to refresh it every
> day
> (just in case!)!
> John
> "Joe from WI" wrote:
>
