结果: 2012-02-29 00:00:00.000
格式：select dbo.f_cmd_GetDate('交车日期为：2012年02月29号') -- 按“月-日-年”格式取值
结果: 2012-02-29 00:00:00.000
格式：select dbo.f_cmd_GetDate('交车日期为：2012-2-30') --
如不能按“月-日-年”，则偿试格式按“月-日-年”格式取值
结果: 2030-12-02 00:00:00.000
/*
功能： 从字符串中抓取日期
历史：
2011-12-21 Chelen 创建
*/
create function f_cmd_GetDate
(
@SDate varchar(50) -- 包含日期的字符串
)
returns DateTime
AS
Begin
declare @RetValue DateTime
declare @Exp varchar(50)
set @Exp = rtrim(ltrim(@SDate))
-- 去空格
set @Exp = replace(@Exp, ' ', '')
-- 统一减号“-”为分隔符
set @Exp = replace(@Exp, '年', '-')
set @Exp = replace(@Exp, '月', '-')
set @Exp = replace(@Exp, '日', '-')
set @Exp = replace(@Exp, '/', '-')
set @Exp = replace(@Exp, '.', '-')
--去掉左边的非法字符
while Len(@Exp) > 0 and Charindex(Left(@Exp,1), '0123456789') = 0
begin
set @Exp = Substring(@Exp, 2, Len(@Exp))
end
--去掉右边的非法字符
while Len(@Exp) > 0 and Charindex(right(@Exp,1), '0123456789') = 0
begin
set @Exp = Substring(@Exp, 1, Len(@Exp)-1)
end
-- 小于 8 个字符已不可能转为日期了，退出
if len(@Exp) < 8 goto ExitFun
--少于 2 个减号“-”不可能转为日期，退出
declare @Count int
select @Count = len(@Exp) - len(replace(@Exp,'-',''))
if @Count < 2 Goto ExitFun
--按减号“-”作为分隔符拆分成N条记录，插入到临时表
declare @Tmp table
(
Row int identity(1,1),
Part varchar(50)
)
declare @p int
set @p = Charindex('-', @Exp)
while @p > 0
begin
insert into @Tmp(Part) values(Substring(@Exp, 1, @p-1))
select @Exp = Substring(@Exp, @p + 1, Len(@Exp)), @p = Charindex('-', @Exp)
if @p = 0
begin
insert into @Tmp(Part) values(@Exp)
set @Exp = ''
end
end
-- 开始偿试转换
declare @s1 varchar(50), @s2 varchar(50), @s3 varchar(50)
declare @Year int, @Month int , @Day int
declare @BofMonth DateTime
declare @Row int, @MaxRow int, @n int
select @Row = 2, @MaxRow = Max(Row) from @Tmp
while @RetValue is null and (@Row <= @MaxRow -1)
begin
select
@s1 = Max(case when Row = @Row -1 then Part else '' end),
@s2 = Max(case when Row = @Row then Part else '' end),
@s3 = Max(case when Row = @Row +1 then Part else '' end)
from @tmp
where Row in (@Row -1, @Row, @Row +1)
select @Year = 0, @Month = 0, @Day = 0
--中间段是数值，偿试转换
if IsNumeric(@s2) = 1 and (Convert(int, @s2) between 1 and 31)
begin
--按: 年-月-日格式转
set @Month = Convert(int, @s2)
if @Month between 1 and 12
begin
-- 取年（从右边取，最多4位数）
set @n = 1
while @n <= 4 and IsNumeric(Right(@S1, @n)) = 1
begin
set @year = convert(int, Right(@S1, @n))
set @n = @n + 1
end
-- 取日（从左边取，最多2位数）
set @n = 1
while @n <= 2 and IsNumeric(Left(@S3, @n)) = 1
begin
set @Day = convert(int, Left(@S3, @n))
set @n = @n + 1
end
--年度为2位或4位，日在1-31间
if ((@Year between 10 and 99) or (@Year between 1000 and 9999)) and (@Day between 1 and 31)
begin
-- 求本月的第1天（被下面用)
set @BofMonth = Convert(DateTime, convert(varchar, @year) + '-' + convert(varchar, @Month) + '-01')
-- 日，在本月的天数内（有效）
if @Day <= day(dateadd(d, -1, dateadd(m, 1, @BofMonth)))
begin
set @RetValue = Convert(DateTime, convert(varchar, @year) + '-' + convert(varchar, @Month) + '-' + convert(varchar, @Day))
end
end
end
if @RetValue is not null Goto ExitFun
--按: 月-日-年格式转
set @Day = Convert(int, @s2)
if @Day between 1 and 31
begin
-- 取月（右取，最多2位数）
set @n = 1
while @n <= 2 and IsNumeric(Right(@S1, @n)) = 1
begin
set @Month = convert(int, Right(@S1, @n))
set @n = @n + 1
end
-- 取年（左取，最多4位数）
set @n = 1
while @n <= 4 and IsNumeric(Left(@S3, @n)) = 1
begin
set @Year = convert(int, Left(@S3, @n))
set @n = @n + 1
end
--年度为2位或4位，月在1-12间
if ((@Year between 10 and 99) or (@Year between 1000 and 9999)) and (@Month between 1 and 12)
begin
-- 求本月的第1天（被下面用)
set @BofMonth = Convert(DateTime, convert(varchar, @year) + '-' + convert(varchar, @Month) + '-01')
-- 日，在本月的天数内（有效）
if @Day <= day(dateadd(d, -1, dateadd(m, 1, @BofMonth)))
begin
set @RetValue = Convert(DateTime, convert(varchar, @year) + '-' + convert(varchar, @Month) + '-' + convert(varchar, @Day))
end
end
end
if @RetValue is not null Goto ExitFun
end
--
set @Row = @Row + 1
end
-- 返回
ExitFun:
return(@RetValue)
End