SQL Server,一个自定义字符串转日期函数

读别人的程序能学到好多东西。好的学习,坏的学习如何改进,实在难以入目的,就引以为戒。

比如这个,日期不用datetime类型保存,用nvarchar,倒是可以,但是完全依赖系统设置在系统移植的时候真是难呐。
这两个一个是英文环境一个是中文环境的日期值:
Oct 1 2009 9:19AM
10 1 2009 9:19AM

没办法写了个函数来转:
[sourcecode language=”sql”]
create function uf_ctod(@datestr varchar(20))
returns datetime
as
begin
declare @st char(2)
declare @rd datetime
declare @s2 char(3)
declare @s3 varchar(20)
declare @sf varchar(20)

set @st = substring(@datestr, 1, 2)
if isnumeric(@st) =1 begin
set @rd = convert(datetime, @datestr, 109)
end else begin
set @s2 = substring(@datestr, 1, 3)
set @sf = substring(@datestr, 4, len(@datestr)-3)
select @s3 = case @s2
when ‘Jan’ then ‘1’
when ‘Feb’ then ‘2’
when ‘Mar’ then ‘3’
when ‘Apr’ then ‘4’
when ‘May’ then ‘5’
when ‘Jun’ then ‘6’
when ‘Jul’ then ‘7’
when ‘Aug’ then ‘8’
when ‘Sep’ then ‘9’
when ‘Oct’ then ’10’
when ‘Nov’ then ’11’
when ‘Dec’ then ’12’ end
set @sf = @s3 + @sf
set @rd = convert(datetime, @sf, 109)
end
return(@rd)
end
[/sourcecode]
再改进一点点:
[sourcecode language=”sql”]
create function uf_c2d(@datestr varchar(20))
returns datetime
begin
declare @rd datetime
select @rd =
case when isnumeric(substring(@datestr, 1, 2)) = 1 then
convert(datetime, @datestr, 109)
else
case substring(@datestr, 1, 3)
when ‘Jan’ then ‘1’
when ‘Feb’ then ‘2’
when ‘Mar’ then ‘3’
when ‘Apr’ then ‘4’
when ‘May’ then ‘5’
when ‘Jun’ then ‘6’
when ‘Jul’ then ‘7’
when ‘Aug’ then ‘8’
when ‘Sep’ then ‘9’
when ‘Oct’ then ’10’
when ‘Nov’ then ’11’
when ‘Dec’ then ’12’
end + substring(@datestr, 4, len(@datestr) -3)
end
return(@rd)
end
[/sourcecode]

打赏

《SQL Server,一个自定义字符串转日期函数》有一个想法

  1. 老师,又来请教你了。我现在想刷新wxlistctrl控件怎么弄?
    我插入数据想时刻让wxlistctrl控件随着时刻更新,在VC中 m_list.DeleteAllItems();
    m_list.SetRedraw(FALSE);
    while(m_list.DeleteColumn(0));
    最后插入之后
    m_list.SetRedraw(TRUE);
    我查看了wxlistctrl的属性中有RefreshItems
    void RefreshItems(long itemFrom, long itemTo)
    Redraws the items between itemFrom and itemTo. The starting item must be less than or equal to the ending one.
    请问这个怎么获取最后一行的 也就是行数?才能达到时刻更新呢?请老师指点一下。

发表评论

电子邮件地址不会被公开。 必填项已用*标注