一般自己写一个函数比较好,模拟Oracle的to_date,to_char一类的函数,下面自己写一个模拟to_char的:
create function [dbo].[UFN_DateToChar](
@arg_date datetime,
@arg_format varchar(50)
)
/***************************************************
* author sf
* 功能:转换日期类型的格式传化为字符串的格式
* @arg_date 输入的日期类型
* @arg_format 这符串的格式
**************************************************/
returns varchar(100)
as
begin
declare @v_return varchar(100)
declare @v_year varchar(10)
declare @v_month varchar(10)
declare @v_day varchar(10)
declare @v_hour varchar(10)
declare @v_minute varchar(10)
declare @v_second varchar(10)
declare @v_millisecond varchar(10)
declare @v_date datetime
declare @v_format varchar(50)
if @arg_date is null
return null
else
set @v_date = @arg_date
set @v_format = isnull(@arg_format,'yyyy-mm-dd hh:mi:ss')
set @v_year = convert(varchar,datepart(year,@v_date))
set @v_month = convert(varchar,datepart(month,@v_date))
set @v_day = convert(varchar,datepart(day,@v_date))
set @v_hour = convert(varchar,datepart(hour,@v_date))
set @v_minute = convert(varchar,datepart(minute,@v_date))
set @v_second = convert(varchar,datepart(second,@v_date))
set @v_millisecond = convert(varchar,datepart(millisecond,@v_date))
-- 进行替换
set @v_return = @arg_format
set @v_return = replace(@v_return,'yyyy',@v_year)
set @v_return = replace(@v_return,'mm',case when len(@v_month)=1 then '0'+@v_month[/email] else @v_month end)
set @v_return = replace(@v_return,'dd',case when len(@v_day)=1 then '0'+@v_day[/email] else @v_day end)
set @v_return = replace(@v_return,'hh',case when len(@v_hour)=1 then '0'+@v_hour[/email] else @v_hour end)
set @v_return = replace(@v_return,'mi',case when len(@v_minute)=1 then '0'+@v_minute[/email] else @v_minute end)
set @v_return = replace(@v_return,'ss',case when len(@v_second)=1 then '0'+@v_second[/email] else @v_second end)
set @v_return = replace(@v_return,'ms',case when len(@v_millisecond)=1 then '0'+@v_millisecond[/email] else @v_millisecond end)
return @v_return
end
-----完------
调用方法:
select dbo.UFN_DateToChar(getdate(),'yyyy-mm-dd hh:mi:ss')
然后自己可再转换回时间
格式串可以随便写,比如'yyyy-mm-dd','yyyy/mm/dd','yyyy年mm'