表里有一个datetime的字段 “2012-04-01 10:30:12.483”
如何将 字段的值转换成 “2012-4-1”
用Convert吗？
------解决方案--------------------------------------------------------
select convert(char(4),YEAR('2012-04-01 10:30:12.483'))+'-'+CONVERT(char(2), month('2012-04-01 10:30:12.483'))+'-'+convert(char(2),DAY('2012-04-01 10:30:12.483'))
------解决方案--------------------------------------------------------
先执行（创建一个构造函数 截取字符串）
CREATE function [dbo].[GetSplitIndex]
(
@String nvarchar(max), --要分割的字符串
@split nvarchar(10), --分隔符号
@index int --取第几个元素
)
returns nvarchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @String=ltrim(rtrim(@String))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@String)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@String,@start)
set @next=@next+1
end
if @location =0 select @location =len(@String)+1
return substring(@String,@start,@location-@start)
end
GO
再执行
select dbo.GetSplitIndex('2012-01-03 12:15:23',' ',1)
------解决方案--------------------------------------------------------
- SQL code
declare @date datetime;set @date='2012-04-01 10:30:00'select replace(convert(varchar(10),@date,120),'-0','-')
------解决方案--------------------------------------------------------
- SQL code
select replace(convert(varchar(10),getdate(),120),'-0','-') as newtimeselect convert(varchar(10),日期,120)--在表中select replace(convert(varchar(10),getdate(),120),'-0','-') as newtime from tablename