求sql触发器: 把插入的日期中的时分秒自动变成全0,把被修改的日期一旦有时分秒也把它们清0

Question

求sql触发器: 把插入的日期中的时分秒自动变成全0,把被修改的日期一旦有时分秒也把它们清0

Answer 1

declare @dt datetime
set @dt=getdate()
select @dt, dateadd(d, datediff(d, 0, @dt), 0)/*
----------------------- -----------------------
2008-06-04 10:43:06.787 2008-06-04 00:00:00.000(1 row(s) affected)
*/

Answer 2

select convert(varchar(14),getdate(),120)+'00:00'

Answer 3

create trigger tr_tb_i on tb before insert
as
  update inserted set datecol = cast(substring(cast (datecol as varchar(20)),1,10)+ "00:00:00:000" as datetime)      

Answer 4

create trigger tr_tb_i on tb before insert
as
  update inserted set datecol = cast(substring(cast (datecol as varchar(20)),1,10)+ '00:00:00:000' as datetime)

Answer 5

create table test(id int,time datetime)
gocreate trigger trg_test on test
instead of insert,update
as
begin
    delete from test from deleted d where test.id=d.id and test.time=d.time
    insert into test select id,convert(char(10),time,120) from inserted
end
goinsert into test select 1,getdate()
insert into test select 2,getdate()
select * from test
/*
id          time                                                   
----------- ------------------------------------------------------ 
1           2008-06-04 00:00:00.000
2           2008-06-04 00:00:00.000
*/update test set time=getdate()-1 where id=1
select * from test
/*
id          time                                                   
----------- ------------------------------------------------------ 
1           2008-06-03 00:00:00.000
2           2008-06-04 00:00:00.000
*/
godrop trigger trg_test
drop table test
go

Answer 6

SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE(), 120))------------------------------------------------------ 
2008-06-04 00:00:00.000

Answer 7

SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE(), 120)) ------------------------------------------------------ 
2008-06-04 00:00:00.000 

Answer 8

也可以直接把该字段设为smalldatetime