有人能帮我创建一个日期等于当前日期加30天的表吗。这是对的吗?
CREATE TABLE SAMPLE
( datetoday DATETIME NOT NULL DEFAULT current_datetime(),
dateafter30days DATETIME NOT NULL DEFAULT current_date(+30)
);
逻辑是,例如,有一个订阅者现在已经注册,因此它将被记录在数据库中,并且他的注册到期时间是30天后。
意思是注册日期和到期日期。。
非常感谢
Joey
您可以为此使用计算字段(可选持久字段):
CREATE TABLE YourTableName
(
Subscriber INT PRIMARY KEY,
IssueDate DATETIME,
ExpireDate AS DATEADD(DAY, 30, IssueDate)
)
create table SAMPLE
(
SUBSCRIBER_ID INT Primary Key,
REGISTER_DT DATETIME NOT NULL,
EXPIRE_DT DATETIME NOT NULL
)
您可以在注册新用户时更新过期日期,如下所示。
INSERT INTO SAMPLE(SUBSCRIBER_ID,REGISTER_DT,EXPIRE_DT)
VALUES (1,GETDATE(), DATEADD(DAY,30,GETDATE())
是的,您可以试试。插入新的订阅者数据时,注册日期为当前日期,过期日期为当前日后30天。您的insert sql语句,不需要提及这两列(REGISTER_DT、EXPIRE_DT),这两列将在插入语句时自动更新。
根据下表结构,您的插入语句应该是
INSERT INTO SAMPLE (SUBSCRIBER_NM) VALUES ('John');
--表创建语句
CREATE TABLE [dbo].[SAMPLE](
[SUBSCRIBER_ID] [int] IDENTITY(1,1) NOT NULL,
[SUBSCRIBER_NM] [nvarchar](50) NOT NULL,
[REGISTER_DT] [datetime] NOT NULL CONSTRAINT [DF_SAMPLE_REGISTER_DT] DEFAULT (getdate()),
[EXPIRE_DT] [datetime] NOT NULL CONSTRAINT [DF_SAMPLE_EXPIRE_DT] DEFAULT (dateadd(day,(30),getdate())),
CONSTRAINT [PK_SAMPLE] PRIMARY KEY CLUSTERED
(
[SUBSCRIBER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
在插入时创建一个表和一个触发器*表格*
create table test
(
sub_id int primary key,
issueDate datetime,
expDate datetime
)
CREATE TRIGGER test_trigger BEFORE INSERT ON `test`
FOR EACH ROW SET NEW.issueDate = IFNULL(NEW.issueDate,NOW()),
NEW.expDate= TIMESTAMPADD(DAY,30,NEW.issueDate)