想要在SQL Server中插入日期的datetime列中默认插入当前时间 [英] Want to insert current time by default in datetime column with inserted date in SQL server

问题描述

嘿，



我想自动插入当前时间，并在datetime列中插入日期。默认情况下插入00:00:00



我尝试过：



i为它创建了触发器。

hey,

I want to insert the current time automatically with the inserted date in datetime column. by default it inserts 00:00:00

What I have tried:

i have created the trigger for it.

Create trigger tr_tm on emp
after insert,update
as
declare @tme time

set @tme=(select CONVERT(varchar(8),start_date,108) from inserted)
set @tmr
update emp
set @tmr=convert(varchar(8),getdate(),108)
where @tme='00:00:00'
go

i希望tmr值成为日期时间列的时间部分。所以我可以只更新那个时间部分。



怎么可能。



提前感谢，

i want the tmr value the time part of the date time column. so thats i can update only tht time part.

how it is possible.

thanks in advance,

推荐答案

您可以尝试在创建表列时使用 GETDATE（）作为默认值。

You could try to use GETDATE() as the default value when you create the table column.

ADD myDateColumn DATETIME NOT NULL DEFAULT (GETDATE());

根据您的具体情况，可能不需要额外的列。如果你永远不改变TIME栏中的值，那么没有它你会更好。

当你需要时，从DATETIME列中提取时间部分。



如果您真的想使用触发器，这个代码示例可能会帮助您。



您可以直接在查询窗口中运行此代码。 br />

Depending on your circumstances it might be no need for an extra column. If you never change the value in the TIME column, then you live better without it.
Extract the time part from the DATETIME column instead when you need it.

If you really wanna use a trigger, this code example might help you along.

You can run this code directly in a query window.

DECLARE @myDate DATETIME2;
DECLARE @myTime TIME;
SET @myDate = GETDATE();
SET @myTime = CONVERT(TIME, @myDate);
SELECT @myTime;

这篇关于想要在SQL Server中插入日期的datetime列中默认插入当前时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！