海兄的已经可以了,不过我还想添添乱 给个函数可以去掉2002-10-04前面的0得到2002-10-4 用select dbo.formatdate(datefield,'YYYY-M1-D1') from tb :)收藏吧create function [dbo].[FormatDate] ( @dDate datetime --Date value to be formatted ,@sFormat varchar(40) --Format for date value ) returns varchar(40) as begin -- Insert the Month -- ~~~~~~~~~~~~~~~~ set @sFormat = replace(@sFormat,'MMMM',datename(month,@dDate)) set @sFormat = replace(@sFormat,'MMM',convert(char(3),datename(month,@dDate))) set @sFormat = replace(@sFormat,'MM',right(convert(char(4),@dDate,12),2)) set @sFormat = replace(@sFormat,'M1',convert(varchar(2),convert(int,right(convert(char(4),@dDate,12),2)))) -- Insert the Day -- ~~~~~~~~~~~~~~ set @sFormat = replace(@sFormat,'DDDD',datename(weekday,@dDate)) set @sFormat = replace(@sFormat,'DDD',convert(char(3),datename(weekday,@dDate))) set @sFormat = replace(@sFormat,'DD',right(convert(char(6),@dDate,12),2)) set @sFormat = replace(@sFormat,'D1',convert(varchar(2),convert(int,right(convert(char(6),@dDate,12),2)))) -- Insert the Year -- ~~~~~~~~~~~~~~~ set @sFormat = replace(@sFormat,'YYYY',convert(char(4),@dDate,112)) set @sFormat = replace(@sFormat,'YY',convert(char(2),@dDate,12)) -- Return the function's value -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~ return @sFormat end
to: Yang_(扬帆破浪) 现在得到的是字符型的,那怎么转换成日期型的?
>>现在得到的是字符型的,那怎么转换成日期型的?select convert(datetime,'2002-10-7') or(除了6.5): select cast('2002-10-7' as datetime)
^_^ ,要不想办法到客户端实现 ?pb里有date函数可以得到 :)
select cast(convert(char(8),datefield,112) as datetime)
给个函数可以去掉2002-10-04前面的0得到2002-10-4
用select dbo.formatdate(datefield,'YYYY-M1-D1') from tb
:)收藏吧create function [dbo].[FormatDate]
(
@dDate datetime --Date value to be formatted
,@sFormat varchar(40) --Format for date value
)
returns varchar(40)
as
begin -- Insert the Month
-- ~~~~~~~~~~~~~~~~
set @sFormat = replace(@sFormat,'MMMM',datename(month,@dDate))
set @sFormat = replace(@sFormat,'MMM',convert(char(3),datename(month,@dDate)))
set @sFormat = replace(@sFormat,'MM',right(convert(char(4),@dDate,12),2))
set @sFormat = replace(@sFormat,'M1',convert(varchar(2),convert(int,right(convert(char(4),@dDate,12),2)))) -- Insert the Day
-- ~~~~~~~~~~~~~~
set @sFormat = replace(@sFormat,'DDDD',datename(weekday,@dDate))
set @sFormat = replace(@sFormat,'DDD',convert(char(3),datename(weekday,@dDate)))
set @sFormat = replace(@sFormat,'DD',right(convert(char(6),@dDate,12),2))
set @sFormat = replace(@sFormat,'D1',convert(varchar(2),convert(int,right(convert(char(6),@dDate,12),2)))) -- Insert the Year
-- ~~~~~~~~~~~~~~~
set @sFormat = replace(@sFormat,'YYYY',convert(char(4),@dDate,112))
set @sFormat = replace(@sFormat,'YY',convert(char(2),@dDate,12)) -- Return the function's value
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~
return @sFormat
end
现在得到的是字符型的,那怎么转换成日期型的?
or(除了6.5):
select cast('2002-10-7' as datetime)
sqlserver6.5能用吗?
或使用 CONVERT:CONVERT (data_type[(length)], expression [, style])
select convert(char(20),datefield,101)