22,209
社区成员
发帖
与我相关
我的任务
分享
select to_timestamp('2010-04-16 16:52:04.093','YYYY-MM-DD HH24:MI:SS.FF3') from dual; 结果是:16-APR-10 04.52.04.093000000 PM
if object_id('dbo.getdt') is not null
drop function dbo.getdt
go
create function dbo.getdt(@dt datetime)
returns nvarchar(30)
as
begin
return(datename(dd,@dt)+'-'+
case month(@dt)
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 8 then 'Aug'
when 9 then 'Sep'
when 10 then 'Oct'
when 11 then 'Nov'
else 'Dec'
end+'-'+right(datename(yy,@dt),2)+' '+
stuff(right('0'+ltrim(stuff(
replace(convert(varchar,@dt,9),':','.')
,1,10,'')),14),13,0,' ')
)
end
go
--调用:
select dbo.getdt('2010-04-16 16:52:04.093')
--result:
--16-Apr-10 04.52.04.093 PM
还有一点点小错误,改一下。16点应该显示为4点if object_id('dbo.getdt') is not null
drop function dbo.getdt
go
create function dbo.getdt(@dt datetime)
returns nvarchar(30)
as
begin
return(select datename(dd,@dt)+'-'+
case month(@dt)
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 8 then 'Aug'
when 9 then 'Sep'
when 10 then 'Oct'
when 11 then 'Nov'
else 'Dec'
end+'-'+right(datename(yy,@dt),2)+' '+
replace(convert(varchar,@dt,14),':','.')+
case when cast(convert(varchar,@dt,8) as datetime)
between '00:00:00' and '11:59:59' then ' AM'
else ' PM'
end)
end
go
--调用:
select dbo.getdt('2010-04-16 16:52:04.093')
--result:
--16-Apr-10 16.52.04.093 PM
--日期转换参数,值得收藏
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
其它我不常用的日期格式转换方法:
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
--convert作用:时间转字符串
--120是一种时间格式,更多格式请百度convert
select convert(varchar,getdate(),120)
--结果
/*2010-04-16 19:15:36*/