Wednesday, April 9, 2014

Find birthday from NIC number (Sri Lanka) PL/SQL code

This program is design to find birthday from NIC number (Sri Lanka). Fell free to comment and optimize the code.

DECLARE
    TYPE daysInMonthArray IS VARRAY(12) OF VARCHAR2(2);
    days daysInMonthArray;
    year1 VARCHAR2(4);
    idText VARCHAR2(12);
    dayOfMonth NUMBER;
    j NUMBER;
    dateOfBirth DATE;
    daysOfYear NUMBER;
    month1 VARCHAR2(4);
BEGIN
    daysOfYear := 0;
    idText := '900780826v'; -- Enter your NIC number here
    days := daysInMonthArray('31','29','31','30','31','30','31','31','30','31','30','31');
    year1 := '19' || SUBSTR(idText,0, 2);
    daysOfYear := SUBSTR(idText,3, 3);
    IF (daysOfYear > 500) THEN
        daysOfYear := daysOfYear-500;
    END IF;


    dayOfMonth := daysOfYear;
   
    j := 0;
   
    FOR i IN days.first .. days.last LOOP
        IF (dayOfMonth > days(i)) THEN
            dayOfMonth := dayOfMonth - days(i);
            j := i;
        ELSIF (dayOfMonth = days(i)) THEN
            dayOfMonth := dayOfMonth;
            j := i;
        ELSE
            EXIT;
        END IF;
    END LOOP;
   
    j := j + 1;
   
    IF (j < 10) THEN
        month1 := '0' || j;
    ELSE
        month1 := J;
    END IF;
    dateOfBirth := TO_DATE(year1 - 1900 || month1 || dayOfMonth , 'YYMMDD');
    DBMS_OUTPUT.PUT_LINE('Date of birth: ' || TO_CHAR(dateOfBirth, 'YY-MM-DD'));
END;
/



2 comments:

  1. T -SQL COMMAND

    DECLARE @daysInMonthArray TABLE ( IDs VARCHAR(100) )
    DECLARE @year1 VARCHAR(4) ,
    @idText VARCHAR(10) ,
    @dayOfMonth INT ,
    @j INT ,
    @dateOfBirth DATE ,
    @daysOfYear INT ,
    @month1 VARCHAR(4) ,
    @days INT

    SET @daysOfYear = 0;
    SET @idText = '851230750v';
    -- Enter your NIC number here
    INSERT INTO @daysInMonthArray
    VALUES ( '31' ),
    ( '29' ),
    ( '31' ),
    ( '30' ),
    ( '31' ),
    ( '30' ),
    ( '31' ),
    ( '31' ),
    ( '30' ),
    ( '31' ),
    ( '30' ),
    ( '31' )

    SET @year1 = '19' + SUBSTRING(@idText, 0, 3);

    SET @daysOfYear = SUBSTRING(@idText, 3, 3);

    SET @days = ( SELECT COUNT(IDs)
    FROM @daysInMonthArray
    )

    IF ( @days > 500 )
    BEGIN
    SET @daysOfYear = @daysOfYear - 500;
    END

    SET @dayOfMonth = @daysOfYear;

    SET @j = 0;

    DECLARE @DATEX VARCHAR(3),
    @x INT
    SET @x =1


    DECLARE @ProductID INT
    DECLARE @getProductID CURSOR
    SET
    @getProductID = CURSOR FOR select (IDs) from @daysInMonthArray
    OPEN @getProductID
    FETCH NEXT FROM @getProductID INTO @DATEX
    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF ( @dayOfMonth > @DATEX )
    BEGIN
    SET @dayOfMonth = @dayOfMonth - CAST(@DATEX AS INT)
    SET @j =@x

    END
    ELSE
    IF ( @dayOfMonth = CAST(@DATEX AS INT) )
    BEGIN
    SET @dayOfMonth = @dayOfMonth;
    SET @j =@x
    END

    SET @x =@x+1
    FETCH NEXT
    FROM @getProductID INTO @DATEX
    END
    CLOSE @getProductID
    DEALLOCATE @getProductID


    SET @j = @j + 1;

    IF ( @j < 10 )
    SET @month1 = '0' + CAST(@j AS VARCHAR)
    ELSE
    SET @month1 = CAST(@j AS VARCHAR)

    PRINT CAST(@year1 AS INT)
    PRINT @month1
    PRINT @dayOfMonth

    --SET @dateOfBirth = TO_DATE(year1 - 1900 || month1 || dayOfMonth , 'YYMMDD');

    --DBMS_OUTPUT.PUT_LINE('Date of birth: ' || TO_CHAR(dateOfBirth, 'YY-MM-DD'));

    ReplyDelete
  2. declare @year int,
    @nic varchar(15),
    @dob int,
    @strfullyear int,
    @dt datetime,
    @dtnew datetime,
    @fullyear int

    set @nic='781953717v'

    set @year = SUBSTRING(@nic,0,3)--@nic.Substring(0, 2)
    set @dob = SUBSTRING(@nic,3,3)--@nic.Substring(2, 3));

    if (@dob >= 500)

    set @dob = @dob - 500;

    set @strfullyear = '19' +CONVERT(varchar(4), @year)
    set @fullyear = @strfullyear



    set @dt = CONVERT(datetime,(CONVERT(varchar(4),@fullyear)+'/'+'01'+'/'+'01'))--ToDate(@fullyear, 1, 1);



    if ((@YEAR % 4 = 0 AND @YEAR % 100 <> 0) OR @YEAR % 400 = 0)
    begin
    set @dtnew = DATEADD(dd,@dob,@dt) --@dt.AddDays(@dob);
    set @dtnew = DATEADD(DD,-1,@dtnew) -- @dtnew.AddDays(-1);

    end
    else
    begin
    if (@dob <= 59)
    begin
    set @dtnew = DATEADD(dd,@dob,@dt) --@dt.AddDays(@dob);
    set @dtnew = DATEADD(DD,-1,@dtnew) -- @dtnew.AddDays(-1);
    select @dtnew
    end
    else
    begin
    set @dtnew = DATEADD(dd,@dob,@dt) --@dt.AddDays(@dob);

    set @dtnew = DATEADD(DD,-2,@dtnew) -- @dtnew.AddDays(-1);

    end
    end

    select @dtnew

    ReplyDelete