IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('EGNToDate')))
THEN Drop procedure EGNToDate
END IF

GO

create function DBA.EGNToDate( in EGN varchar(15) ) 
returns date
begin
  declare CleanEGN varchar(15);
  declare YY integer;
  declare MM integer;
  declare DD integer;
  declare BirthYear integer;
  declare BirthMonth integer;
  set CleanEGN = Trim(EGN);
  if CleanEGN is null or Length(CleanEGN) < 6 then 
    return null
  end if;
  if Length(CleanEGN) = 9 then
    set CleanEGN = '0' || CleanEGN
  end if;
  if Length(CleanEGN) < 10 then
    return null
  end if;
  set YY = Cast(SubStr(CleanEGN, 1, 2) as integer);
  set MM = Cast(SubStr(CleanEGN, 3, 2) as integer);
  set DD = Cast(SubStr(CleanEGN, 5, 2) as integer);
  if (MM >= 41) and (MM <= 52) then
    set BirthYear = 2000 + YY;
    set BirthMonth = MM - 40;
  else
    if (MM >= 21) and (MM <= 32) then
      set BirthYear = 1800 + YY;
      set BirthMonth = MM - 20;
    else	
      set BirthYear = 1900 + YY;
      set BirthMonth = MM;
	end if;  
  end if;
  return Cast(String(BirthYear,'-', Right('0'||String(BirthMonth),2),'-',Right('0' || String(DD), 2)) as date)
end
//[v 00.06.30]
//[v 25.02.2004] Jorko i za EGN-ta sled 2000 godina
//[v 08.03.2004] Jorko i za EGN-ta sled 2000 godina
//[v 04.06.2026] yordan fix EGN sled 2000 godina 

GO