如何每年更新数据库字段

发布于 2024-10-25 06:52:45 字数 225 浏览 1 评论 0原文

我有一个数据库,其中包含 2 个名为 DateOfBirthAge 的字段,分别用于存储用户出生日期和年龄。我希望根据 DOB 匹配服务器日期,Age 列每年自动增加 1。

实现这一目标的最佳方法是什么?我正在使用 asp.netsql server 2008

I have a database which contains 2 fields called DateOfBirth and Age, for storing users DOB and age respectively. I want the Age column to be automatically incremented by 1 every year, according to the DOB matching server date.

What could be the best way for achieving this? I am using asp.net and sql server 2008.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

不要同时存储 DateOfBirthAge,而是创建一个 计算列在计算年龄的表上:

[Age] AS datediff(year,  DateOfBirth, getdate()) 

因此在 yout 表创建中:

-- Create Table with computed column
CREATE TABLE [dbo].[CCtest]
(
    [id] [int] not NULL,
    [DateOfBirth] [datetime] NULL,
    -- etc...
    [Age] AS datediff(year,  DateOfBirth, getdate()) 
)
GO 

如果要保留计算值,请添加 PERSISTED 关键字。

一种可能性是,如果您希望以年和月显示年龄:

    [AgeInDays] AS datediff(day,  DateOfBirth, getdate()) 

然后在表格上创建一个视图,将 AgeInDays 格式设置为年和月。

这是另一种可能性,使用 [AgeYears] 的计算列:

create view vwCCtestAge
AS
select 
   id, 
   dateofbirth,
   cast([AgeYears] as varchar(4)) + ' years ' + 
      cast(datediff(month, DateOfBirth, getdate()) 
           - case when (AgeYears > 0) then (AgeYears - 1)*12 
                  else 0 
             end as varchar(4)) + ' months' as Age
   from cctest2
   GO

[您应该检查边界情况...]

Rather than store both the DateOfBirth and Age, create a computed column on the table that calculates the age:

[Age] AS datediff(year,  DateOfBirth, getdate()) 

So in yout table creation:

-- Create Table with computed column
CREATE TABLE [dbo].[CCtest]
(
    [id] [int] not NULL,
    [DateOfBirth] [datetime] NULL,
    -- etc...
    [Age] AS datediff(year,  DateOfBirth, getdate()) 
)
GO 

If you want to persist the computed value add the PERSISTED keyword.

One possibility, if you want Age displayed in years and months:

    [AgeInDays] AS datediff(day,  DateOfBirth, getdate()) 

then create a view over your table that formats AgeInDays into years and months.

Here is another possibility, using a computed column of [AgeYears]:

create view vwCCtestAge
AS
select 
   id, 
   dateofbirth,
   cast([AgeYears] as varchar(4)) + ' years ' + 
      cast(datediff(month, DateOfBirth, getdate()) 
           - case when (AgeYears > 0) then (AgeYears - 1)*12 
                  else 0 
             end as varchar(4)) + ' months' as Age
   from cctest2
   GO

[You should check for boundary cases...]

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文