今天写的Sql语句,对日期格式进行规范的.
很长,还没写完,今天晚上要接着写.
今天上午已把SQL写好,见下: --By 2005-06-28
建造函数的SQL:
执行的SQL:
今天上午已把SQL写好,见下: --By 2005-06-28
建造函数的SQL:
create function CheckCharNum
--检测字符串中有几个字符,返回查找到的数量
--比如 select dbo.CheckCharNum('-','2002-1-1') 就会返回2
(
@findStr varchar(10),
@srcStr varchar(30)
)
returns int
as
begin
declare @num int
declare @site int
set @num=0
set @site = charindex(@findStr,@srcStr,0) --从开始找起
while @site>0
begin
set @num = @num+1
set @site = charindex(@findStr,@srcStr,@site+1)
end
return @num
end
go
create function CheckIsNum --检测一个字符串是不是一整数
(
@value varchar(20)
)
returns int
as
begin
declare @isNum int
if charindex('.',@value)=0 and isNumeric(@value)=1
set @isNum=1
else
set @isNum=0
return @isNum
end
go
--1999..2.2
--update pi_CZRKDJB set birth=replace(birth,'..','.') where charindex('..',birth)>0
--1999.2.2 0:0
--update pi_CZRKDJB set birth=replace(birth,' 0:0','') where charindex(' 0:0',birth)>0
--字符长度为1
--update pi_CZRKDJB set birth='' where len(birth)=1
--字符长度为2,建造函数
create function SetTwoChar
(
@datestr varchar(2)
)
returns varchar(4)
as
begin
declare @retvalue varchar(4)
if isNumeric(@datestr)=1
begin
set @retvalue='19'+@datestr+'-01-01' --1999-01-01
end
else
begin
set @retvalue=''
end
return @retvalue
end
go
--对字符长度为2的日期进行格式化.
--update pi_CZRKDJB set birth=dbo.SetTwoChar(birth) where len(birth)=2
--字符长度为3,建造函数
create function SetThreeChar
(
@Datestr varchar(3)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if charindex('.',@DateStr)>0 --如果是56.这种格式,则变为1956.01.01
begin
set @formatdate ='19'+substring(@Datestr,1,2)+'-01-01'
end
else --如果是35岁这种格式
begin
set @formatdate=''
end
return @formatdate
end
go
--
--对字符长度为3的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetThreeChar(birth) where len(birth)=3
--字符串长度为4,建造函数
create function SetFourChar
(
@Datestr varchar(4)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
declare @leftstr varchar(4)
declare @rightstr varchar(4)
declare @split int
set @formatdate=''
if charindex('.',@DateStr)>0 --如果是56.1这种格式,则变为1956.01.01
begin
set @split = charindex('.',@DateStr)
set @leftstr = substring(@DateStr,1,@split-1)
set @rightstr = substring(@DateStr,@split+1,len(@DateStr))
if dbo.CheckIsNum(@leftstr)=1 and dbo.CheckIsNum(@rightstr)=1
begin
set @formatdate = '19'+@leftstr+'-0'+@rightstr+'-01'
end
else
begin
set @formatdate = ''
end
end
else --如果是1956这种格式
begin
if dbo.CheckIsNum(@Datestr)=1
set @formatdate=@Datestr+'-01-01'
else
set @formatdate=''
end
return @formatdate
end
go
--对字符长度为4的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetFourChar(birth) where len(birth)=4
--字符串长度为5,建造函数
create function SetFiveChar
--长度为5的格式有:61.11 ,47.3.,1956.,1927年,19885,199.7,61/11
(
@DateStr varchar(5)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if charindex('.',@DateStr)>0 --如果有.的话,只可能位置在3与5是正确的
begin
if charindex('.',@DateStr)=3 -- '77.12'这种格式
set @formatdate = '19'+substring(@DateStr,1,2)+'-'+substring(@DateStr,4,2)+'-01'
else if charindex('.',@DateStr)=5 --1999.这种格式
set @formatdate = substring(@DateStr,1,4)+'-01-01'
else
set @formatdate = ''
end
else
begin
if charindex('/',@DateStr)=3 --57/12这种格式
set @formatdate = '19'+substring(@DateStr,1,2)+'-'+substring(@DateStr,4,2)+'-01'
else
set @formatdate=''
end
return @formatdate
end
go
----对字符长度为5的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetFiveChar(birth) where len(birth)=5
--字符串长度为6,建造函数
create function SetSixChar
--长度为6的格式有:1960.9 , 98.4.6 ,1960-4 , 71/1/8, 1931/7 , 194702 ,1970。2
(
@DateStr varchar(6)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
--提出1960.1 1960-1 1960/1 这种格式
if dbo.CheckCharNum('-',@DateStr)=1 or dbo.CheckCharNum('.',@DateStr)=1 or dbo.CheckCharNum('/',@DateStr)=1
set @formatdate = substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-01'
--提出48.2.1 48/2/1 48-2-1这种格式
else
begin
if dbo.CheckCharNum('-',@DateStr)=2 or dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2
set @formatdate = '19'+substring(@DateStr,1,2)+'-0'+substring(@DateStr,4,1)+'-0'+substring(@DateStr,6,1)
else
set @formatdate = ''
end
return @formatdate
end
go
--对字符长度为6的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetSixChar(birth) where len(birth)=6
--字符串长度为7,建造函数
create function SetSevenChar
--长度为7的格式有:1960.11 , 1955.02 , 57.11.7 ,64.1.22 ,1957-09 , 74/9/18,1997年9月, 1963/11
(
@DateStr varchar(7)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if dbo.CheckCharNum('.',@DateStr)=1 or dbo.CheckCharNum('-',@DateStr)=1 or dbo.CheckCharNum('/',@DateStr)=1 --如果是1960.11 1955.02 1960-11 1960/11这种格式
begin
if dbo.CheckIsNum(substring(@DateStr,1,4))=1
set @formatdate=substring(@DateStr,1,4)+'-'+substring(@DateStr,6,2)+'-01'
else
set @formatdate=''
end
else if dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2 --如果是51.7.17 , 51.11.7 51/11/2 这种格式
begin
if dbo.CheckIsNum(substring(@DateStr,4,2))=1 --表示51.11.7 51/11/7这种格式
set @formatdate='19'+substring(@DateStr,1,2)+'-'+substring(@DateStr,4,2)+'-0'+substring(@DateStr,7,1)
else --表示51.1.17 51/1/17这种格式
set @formatdate='19'+substring(@DateStr,1,2)+'-0'+substring(@DateStr,4,1)+'-'+substring(@Datestr,6,2)
end
else if charindex('年',@DateStr)>0 --表示1969年2月
begin
set @formatdate=substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-01'
end
else
set @formatdate=''
return @formatdate
end
go
--对字符长度为7的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetSevenChar(birth) where len(birth)=7
create function SetEightChar
--长度为8的格式有:1931.6.9 78.11.20 1956-8-4 50/12/19 1950-5-6
(
@DateStr varchar(8)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if dbo.CheckCharNum('-',@DateStr)=2 or dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2
begin
if dbo.CheckIsNum(substring(@DateStr,1,4))=1
--如果是1940.7.6 1940/7/6 1940-7-6
set @formatdate=substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-0'+substring(@DateStr,8,1)
else if dbo.CheckIsNum(substring(@DateStr,1,2))=1 and dbo.CheckIsNum(substring(@DateStr,4,2))=1 --如果是40-12-30 40/12/30这种格式
set @formatdate='19'+substring(@DateStr,1,2)+'-'+substring(@DateStr,4,2)+'-'+substring(@DateStr,7,2)
else
set @formatdate=''
end
else
set @formatdate=''
return @formatdate
end
go
--对字符长度为8的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetEightChar(birth) where len(birth)=8
--字符串长度为9,建造函数
create function SetNineChar
--长度为9的格式有:1931.12.9 1931.1.19 1931-12-9 1931-1-19 1950年1月9号 198510.31
(
@DateStr varchar(9)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if dbo.CheckCharNum('-',@DateStr)=2 or dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2 --1931.12.9 1931.1.19 1931-12-9 1931-1-19
begin
if dbo.CheckIsNum(substring(@DateStr,1,4))=1
begin
if dbo.CheckIsNum(substring(@DateStr,6,2))=1 --1931.12.9 1931-12-9 1931/12/9
set @formatdate = substring(@DateStr,1,4)+'-'+substring(@DateStr,6,2)+'-0'+substring(@DateStr,9,1)
else if dbo.CheckIsNum(substring(@DateStr,8,2))=1 --1931.1.13 1931/1/13 1931-1-13
set @formatdate = substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-'+substring(@DateStr,8,2)
else
set @formatdate=''
end
else
set @formatdate=''
end
else if charindex('年',@DateStr)>0
set @formatdate=substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-01'--+substring(@DateStr,8,1)
else
set @formatdate=''
return @formatdate
end
go
--对字符长度为9的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetNineChar(birth) where len(birth)=9
--字符串长度为10,建造函数
create function SetTenChar
--长度为10的格式
(
@DateStr varchar(10)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2 --1931.12.09 1931.01.19 1931/12/09 1931/01/19
begin
if dbo.CheckIsNum(substring(@DateStr,1,4))=1 and dbo.CheckIsNum(substring(@DateStr,6,2))=1 and dbo.CheckIsNum(substring(@DateStr,9,2))=1
set @formatdate=substring(@DateStr,1,4)+'-'+substring(@DateStr,6,2)+'-'+substring(@DateStr,9,2)
else
set @formatdate=''
end
else if dbo.CheckCharNum('-',@DateStr)=2
set @formatdate=@DateStr
else
set @formatdate=''
return @formatdate
end
go
--对字符长度为10的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)=10
--字符串长度大于10,建造函数
create function SetBigTenChar
--长度大于10的格式
(
@DateStr varchar(30)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if charindex('年',@DateStr)>0
set @formatdate= substring(@DateStr,1,4)+'-'+substring(@DateStr,6,2)+'-'+substring(@DateStr,9,2)
else
set @formatdate=''
return @formatdate
end
go
--对字符长度大于10的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)>10
--检测字符串中有几个字符,返回查找到的数量
--比如 select dbo.CheckCharNum('-','2002-1-1') 就会返回2
(
@findStr varchar(10),
@srcStr varchar(30)
)
returns int
as
begin
declare @num int
declare @site int
set @num=0
set @site = charindex(@findStr,@srcStr,0) --从开始找起
while @site>0
begin
set @num = @num+1
set @site = charindex(@findStr,@srcStr,@site+1)
end
return @num
end
go
create function CheckIsNum --检测一个字符串是不是一整数
(
@value varchar(20)
)
returns int
as
begin
declare @isNum int
if charindex('.',@value)=0 and isNumeric(@value)=1
set @isNum=1
else
set @isNum=0
return @isNum
end
go
--1999..2.2
--update pi_CZRKDJB set birth=replace(birth,'..','.') where charindex('..',birth)>0
--1999.2.2 0:0
--update pi_CZRKDJB set birth=replace(birth,' 0:0','') where charindex(' 0:0',birth)>0
--字符长度为1
--update pi_CZRKDJB set birth='' where len(birth)=1
--字符长度为2,建造函数
create function SetTwoChar
(
@datestr varchar(2)
)
returns varchar(4)
as
begin
declare @retvalue varchar(4)
if isNumeric(@datestr)=1
begin
set @retvalue='19'+@datestr+'-01-01' --1999-01-01
end
else
begin
set @retvalue=''
end
return @retvalue
end
go
--对字符长度为2的日期进行格式化.
--update pi_CZRKDJB set birth=dbo.SetTwoChar(birth) where len(birth)=2
--字符长度为3,建造函数
create function SetThreeChar
(
@Datestr varchar(3)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if charindex('.',@DateStr)>0 --如果是56.这种格式,则变为1956.01.01
begin
set @formatdate ='19'+substring(@Datestr,1,2)+'-01-01'
end
else --如果是35岁这种格式
begin
set @formatdate=''
end
return @formatdate
end
go
--
--对字符长度为3的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetThreeChar(birth) where len(birth)=3
--字符串长度为4,建造函数
create function SetFourChar
(
@Datestr varchar(4)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
declare @leftstr varchar(4)
declare @rightstr varchar(4)
declare @split int
set @formatdate=''
if charindex('.',@DateStr)>0 --如果是56.1这种格式,则变为1956.01.01
begin
set @split = charindex('.',@DateStr)
set @leftstr = substring(@DateStr,1,@split-1)
set @rightstr = substring(@DateStr,@split+1,len(@DateStr))
if dbo.CheckIsNum(@leftstr)=1 and dbo.CheckIsNum(@rightstr)=1
begin
set @formatdate = '19'+@leftstr+'-0'+@rightstr+'-01'
end
else
begin
set @formatdate = ''
end
end
else --如果是1956这种格式
begin
if dbo.CheckIsNum(@Datestr)=1
set @formatdate=@Datestr+'-01-01'
else
set @formatdate=''
end
return @formatdate
end
go
--对字符长度为4的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetFourChar(birth) where len(birth)=4
--字符串长度为5,建造函数
create function SetFiveChar
--长度为5的格式有:61.11 ,47.3.,1956.,1927年,19885,199.7,61/11
(
@DateStr varchar(5)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if charindex('.',@DateStr)>0 --如果有.的话,只可能位置在3与5是正确的
begin
if charindex('.',@DateStr)=3 -- '77.12'这种格式
set @formatdate = '19'+substring(@DateStr,1,2)+'-'+substring(@DateStr,4,2)+'-01'
else if charindex('.',@DateStr)=5 --1999.这种格式
set @formatdate = substring(@DateStr,1,4)+'-01-01'
else
set @formatdate = ''
end
else
begin
if charindex('/',@DateStr)=3 --57/12这种格式
set @formatdate = '19'+substring(@DateStr,1,2)+'-'+substring(@DateStr,4,2)+'-01'
else
set @formatdate=''
end
return @formatdate
end
go
----对字符长度为5的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetFiveChar(birth) where len(birth)=5
--字符串长度为6,建造函数
create function SetSixChar
--长度为6的格式有:1960.9 , 98.4.6 ,1960-4 , 71/1/8, 1931/7 , 194702 ,1970。2
(
@DateStr varchar(6)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
--提出1960.1 1960-1 1960/1 这种格式
if dbo.CheckCharNum('-',@DateStr)=1 or dbo.CheckCharNum('.',@DateStr)=1 or dbo.CheckCharNum('/',@DateStr)=1
set @formatdate = substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-01'
--提出48.2.1 48/2/1 48-2-1这种格式
else
begin
if dbo.CheckCharNum('-',@DateStr)=2 or dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2
set @formatdate = '19'+substring(@DateStr,1,2)+'-0'+substring(@DateStr,4,1)+'-0'+substring(@DateStr,6,1)
else
set @formatdate = ''
end
return @formatdate
end
go
--对字符长度为6的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetSixChar(birth) where len(birth)=6
--字符串长度为7,建造函数
create function SetSevenChar
--长度为7的格式有:1960.11 , 1955.02 , 57.11.7 ,64.1.22 ,1957-09 , 74/9/18,1997年9月, 1963/11
(
@DateStr varchar(7)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if dbo.CheckCharNum('.',@DateStr)=1 or dbo.CheckCharNum('-',@DateStr)=1 or dbo.CheckCharNum('/',@DateStr)=1 --如果是1960.11 1955.02 1960-11 1960/11这种格式
begin
if dbo.CheckIsNum(substring(@DateStr,1,4))=1
set @formatdate=substring(@DateStr,1,4)+'-'+substring(@DateStr,6,2)+'-01'
else
set @formatdate=''
end
else if dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2 --如果是51.7.17 , 51.11.7 51/11/2 这种格式
begin
if dbo.CheckIsNum(substring(@DateStr,4,2))=1 --表示51.11.7 51/11/7这种格式
set @formatdate='19'+substring(@DateStr,1,2)+'-'+substring(@DateStr,4,2)+'-0'+substring(@DateStr,7,1)
else --表示51.1.17 51/1/17这种格式
set @formatdate='19'+substring(@DateStr,1,2)+'-0'+substring(@DateStr,4,1)+'-'+substring(@Datestr,6,2)
end
else if charindex('年',@DateStr)>0 --表示1969年2月
begin
set @formatdate=substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-01'
end
else
set @formatdate=''
return @formatdate
end
go
--对字符长度为7的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetSevenChar(birth) where len(birth)=7
create function SetEightChar
--长度为8的格式有:1931.6.9 78.11.20 1956-8-4 50/12/19 1950-5-6
(
@DateStr varchar(8)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if dbo.CheckCharNum('-',@DateStr)=2 or dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2
begin
if dbo.CheckIsNum(substring(@DateStr,1,4))=1
--如果是1940.7.6 1940/7/6 1940-7-6
set @formatdate=substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-0'+substring(@DateStr,8,1)
else if dbo.CheckIsNum(substring(@DateStr,1,2))=1 and dbo.CheckIsNum(substring(@DateStr,4,2))=1 --如果是40-12-30 40/12/30这种格式
set @formatdate='19'+substring(@DateStr,1,2)+'-'+substring(@DateStr,4,2)+'-'+substring(@DateStr,7,2)
else
set @formatdate=''
end
else
set @formatdate=''
return @formatdate
end
go
--对字符长度为8的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetEightChar(birth) where len(birth)=8
--字符串长度为9,建造函数
create function SetNineChar
--长度为9的格式有:1931.12.9 1931.1.19 1931-12-9 1931-1-19 1950年1月9号 198510.31
(
@DateStr varchar(9)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if dbo.CheckCharNum('-',@DateStr)=2 or dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2 --1931.12.9 1931.1.19 1931-12-9 1931-1-19
begin
if dbo.CheckIsNum(substring(@DateStr,1,4))=1
begin
if dbo.CheckIsNum(substring(@DateStr,6,2))=1 --1931.12.9 1931-12-9 1931/12/9
set @formatdate = substring(@DateStr,1,4)+'-'+substring(@DateStr,6,2)+'-0'+substring(@DateStr,9,1)
else if dbo.CheckIsNum(substring(@DateStr,8,2))=1 --1931.1.13 1931/1/13 1931-1-13
set @formatdate = substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-'+substring(@DateStr,8,2)
else
set @formatdate=''
end
else
set @formatdate=''
end
else if charindex('年',@DateStr)>0
set @formatdate=substring(@DateStr,1,4)+'-0'+substring(@DateStr,6,1)+'-01'--+substring(@DateStr,8,1)
else
set @formatdate=''
return @formatdate
end
go
--对字符长度为9的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetNineChar(birth) where len(birth)=9
--字符串长度为10,建造函数
create function SetTenChar
--长度为10的格式
(
@DateStr varchar(10)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if dbo.CheckCharNum('.',@DateStr)=2 or dbo.CheckCharNum('/',@DateStr)=2 --1931.12.09 1931.01.19 1931/12/09 1931/01/19
begin
if dbo.CheckIsNum(substring(@DateStr,1,4))=1 and dbo.CheckIsNum(substring(@DateStr,6,2))=1 and dbo.CheckIsNum(substring(@DateStr,9,2))=1
set @formatdate=substring(@DateStr,1,4)+'-'+substring(@DateStr,6,2)+'-'+substring(@DateStr,9,2)
else
set @formatdate=''
end
else if dbo.CheckCharNum('-',@DateStr)=2
set @formatdate=@DateStr
else
set @formatdate=''
return @formatdate
end
go
--对字符长度为10的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)=10
--字符串长度大于10,建造函数
create function SetBigTenChar
--长度大于10的格式
(
@DateStr varchar(30)
)
returns varchar(10)
as
begin
declare @formatdate varchar(10)
if charindex('年',@DateStr)>0
set @formatdate= substring(@DateStr,1,4)+'-'+substring(@DateStr,6,2)+'-'+substring(@DateStr,9,2)
else
set @formatdate=''
return @formatdate
end
go
--对字符长度大于10的日期进行格式化
--update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)>10
执行的SQL:
--1999..2.2
update pi_CZRKDJB set birth=replace(birth,'..','.') where charindex('..',birth)>0
--1999.2.2 0:0
update pi_CZRKDJB set birth=replace(birth,' 0:0','') where charindex(' 0:0',birth)>0
--字符长度为1
update pi_CZRKDJB set birth='' where len(birth)=1
--对字符长度为2的日期进行格式化.
update pi_CZRKDJB set birth=dbo.SetTwoChar(birth) where len(birth)=2
--对字符长度为3的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetThreeChar(birth) where len(birth)=3
--对字符长度为4的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetFourChar(birth) where len(birth)=4
----对字符长度为5的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetFiveChar(birth) where len(birth)=5
--对字符长度为6的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetSixChar(birth) where len(birth)=6
--对字符长度为7的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetSevenChar(birth) where len(birth)=7
--对字符长度为8的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetEightChar(birth) where len(birth)=8
--对字符长度为9的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetNineChar(birth) where len(birth)=9
--对字符长度为10的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)=10
--对字符长度大于10的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)>10
update pi_CZRKDJB set birth=replace(birth,'..','.') where charindex('..',birth)>0
--1999.2.2 0:0
update pi_CZRKDJB set birth=replace(birth,' 0:0','') where charindex(' 0:0',birth)>0
--字符长度为1
update pi_CZRKDJB set birth='' where len(birth)=1
--对字符长度为2的日期进行格式化.
update pi_CZRKDJB set birth=dbo.SetTwoChar(birth) where len(birth)=2
--对字符长度为3的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetThreeChar(birth) where len(birth)=3
--对字符长度为4的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetFourChar(birth) where len(birth)=4
----对字符长度为5的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetFiveChar(birth) where len(birth)=5
--对字符长度为6的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetSixChar(birth) where len(birth)=6
--对字符长度为7的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetSevenChar(birth) where len(birth)=7
--对字符长度为8的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetEightChar(birth) where len(birth)=8
--对字符长度为9的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetNineChar(birth) where len(birth)=9
--对字符长度为10的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)=10
--对字符长度大于10的日期进行格式化
update pi_CZRKDJB set birth=dbo.SetTenChar(birth) where len(birth)>10