欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server: Datetime,Datetime2

程序员文章站 2023-04-06 08:35:22
select CONVERT(nvarchar(50), '2018-10-10 10:13:32.000', 126) select convert(nvarchar(MAX), '2018-10-10 10:13:32.999',126) -- 考试记录(员工答题答案,项目ID,题目ID,员工I... ......
select convert(nvarchar(50), '2018-10-10 10:13:32.000', 126) 
select convert(nvarchar(max), '2018-10-10 10:13:32.999',126)


-- 考试记录(员工答题答案,项目id,题目id,员工id,原答案,要顯示測驗日期時間、答案(只有英文字母都 還有同事姓名及分行
if exists (select * from sysobjects where id = object_id(n'[dbo].vipexamanswerrecord') and objectproperty(id, n'isusertable') = 1) 
drop table vipexamanswerrecord 
go
create table vipexamanswerrecord
(
answerrecordid int identity(1,1) primary key,
answerexamprojectid int not null,	--项目id
answerbranchstaffid int not null,	--员工id
answerexamquestionsid int not null,	--问题id
answeraskanswer nvarchar(100) null, -- 考試者選的答案
answerquestionsanswer nvarchar(100) not null, -- 原题正确答案
answerisok bit default(0), -- 1正确,0不正确
answerrecorddate datetime2 default(getdate())	--添加时间

)
go
-- datetime	2007-05-08 12:35:29.123
-- datetime2	2007-05-08 12:35:29. 1234567


declare @d1 datetime ,@d2 datetime2
set @d1=getdate()
set @d2=getdate()
select @d1,@d2
go


select getdate();
go

select convert(varchar(28), getdate(), 126)

if exists (select * from sysobjects where [name] = 'proc_select_vipexamanswerstaffdatesearch')
drop procedure proc_select_vipexamanswerstaffdatesearch
go
create procedure proc_select_vipexamanswerstaffdatesearch
(
	
	@answerbranchstaffid int,
	@answerexamprojectid int,	
	@statdatetime datetime,  -- 秒后小数只有3位  datetime2 秒后小数有7位,根据数据库的设定类型,在程序中也要考虑其一致,否则有查询上就有误差 geovin du  涂聚文提示
	@enddatetime datetime -- 秒后小数只有3位
)
as
   declare @where nvarchar(500),@sql nvarchar(800)	
    set @sql='select [branchstaffname],[branchstaffno],[branchstaffcode],branchname,[examquestionstitle],examprojecttitle,[answeraskanswer],[answerquestionsanswer],[answerisok],[answerrecorddate] from view_vipexamanswerrecordhk where [answerrecorddate]>='''+convert(nvarchar(50),@statdatetime,126)+''' and [answerrecorddate]<='''+convert(nvarchar(50),@enddatetime,126)+''''
    set @where=''
	set @where=@where+' and answerbranchstaffid='+cast(@answerbranchstaffid as varchar)+' and answerexamprojectid='+cast(@answerexamprojectid as varchar)

set @sql=@sql+@where+' order by answerrecorddate desc'
print(@sql)
exec(@sql)
go

exec proc_select_vipexamanswerstaffdatesearch 2631,1,'2018-10-10 10:13:32.000','2018-10-10 10:21:43.999'
go

select convert(nvarchar(50), '2018-10-10 10:13:32.000', 126) 
select convert(nvarchar(max), '2018-10-10 10:13:32.999',126)

 

convert.todatetime(eval("examrecordendtime")).tostring("yyyy-mm-dd hh:mm:ss.fff