Thursday, 16 August 2012

Sql Server Function Returns date in YYYYMMDD Format

Create FUNCTION [dbo].[fn_YMDDate]
(
      @dateParam     varchar(10)
)
RETURNS Varchar(10)
AS
BEGIN
      DECLARE @Return Varchar(10)

      Declare @YYYY  varchar(4)
      Declare @MM  varchar(2)
      Declare @DD  varchar(2)
      Declare @TMP varchar(2)
      Declare @strRemain  varchar(10)
      Declare @strSeprator  varchar(1)
      Declare @strOutPutSeprator  varchar(1)


      if  CHARINDEX ('/',@dateParam ,1)>0
            Begin
                  set @strSeprator='/'
            End
      Else
      if  CHARINDEX ('-',@dateParam ,1)>0
            Begin
                  set @strSeprator='-'
            End

      if  CHARINDEX ('.',@dateParam ,1)>0
            Begin
                  set @strSeprator='.'
            End           
       set @strOutPutSeprator = '.'    

      if  CHARINDEX (@strSeprator,@dateParam ,1)>0
            Begin
                  if  CHARINDEX (@strSeprator,@dateParam ,1) > 4 /*YYYY/MM/DD Format*/
                        Begin

                              set @YYYY=SUBSTRING(@dateParam,1 ,CHARINDEX(@strSeprator,@dateParam,1))
                              set @strRemain=SUBSTRING(@dateParam, CHARINDEX (@strSeprator,@dateParam,1)+1,len(@dateParam))
                              set @MM=SUBSTRING(@strRemain,1 ,(CHARINDEX(@strSeprator,@strRemain,1)-1))
                              set @DD=SUBSTRING(@strRemain, CHARINDEX (@strSeprator,@strRemain,1)+1,len(@strRemain))
                              IF CAST(@MM As int) > 12
                                BEGIN
                                    SET @TMP = @MM
                                    SET @MM = @DD
                                    SET @DD = @TMP
                                END

                        END
                  else
                        Begin
                              set @DD=SUBSTRING(@dateParam,1 ,(CHARINDEX (@strSeprator,@dateParam,1)-1))
                              set @strRemain=SUBSTRING(@dateParam, CHARINDEX (@strSeprator,@dateParam,1)+1,len(@dateParam))
                              set @MM=SUBSTRING(@strRemain,1,(CHARINDEX(@strSeprator,@strRemain,1)-1))
                              set @YYYY=SUBSTRING(@strRemain, CHARINDEX (@strSeprator,@strRemain,1)+1,len(@strRemain))

                              IF CAST(@MM As int) > 12
                                BEGIN
                                    SET @TMP = @MM
                                    SET @MM = @DD
                                    SET @DD = @TMP
                                END
                             
                        End

                        if len(@MM)=1
                       Begin
                              Set @MM='0'+@MM
                        End
                        if len(@DD)=1
                        Begin
                              Set @DD='0' +@DD
                        End

                        SET @Return =  @YYYY + @strOutPutSeprator + @MM + @strOutPutSeprator + @DD

     END
      RETURN ISNULL(@Return,'')
END

No comments: