今天写的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

执行的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