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;
/
T -SQL COMMAND
ReplyDeleteDECLARE @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'));
declare @year int,
ReplyDelete@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
Hi All,
ReplyDeleteWill you guys be able to get me the T-Sql command for both NIC length 10 & 12 to get Date Of Birth?
Thanks,
my date of birth 9-5-1979 can u tell me my NIC number
ReplyDeleteAbove sql's have some issues. please check below one.
ReplyDeleteFirst create function in oracle db.
create or replace FUNCTION GET_BDAY_FRM_NIC_3 ( V_NIC VARCHAR2) return DATE is
BEGIN
DECLARE
V_YEAR VARCHAR2(4);
V_DAYS NUMBER;
V_DAY NUMBER;
V_MONTH VARCHAR2(4);
V_LEAP_YR_STATUS varchar2(5);
-- B_DAY VARCHAR2(10);
B_DAY DATE;
BEGIN
IF REGEXP_LIKE (V_NIC ,'(^\d{9}(V|X|v|x)$)') THEN
V_YEAR := 19||SUBSTR(V_NIC,0,2);
V_DAYS := TO_NUMBER(SUBSTR(V_NIC,3,3));
ELSIF REGEXP_LIKE(V_NIC , '(^(19|20)\d{10}$)') THEN
V_YEAR := SUBSTR(V_NIC,0,4);
V_DAYS := TO_NUMBER(SUBSTR(V_NIC,5,3));
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID_NIC');
END IF;
IF V_DAYS >500 THEN
V_DAYS:= V_DAYS -500;
END IF;
IF (V_DAYS <1 OR V_DAYS>366 OR V_YEAR <1921 OR V_YEAR > TO_CHAR(TRUNC(SYSDATE),'YYYY') ) THEN
V_YEAR:= NULL; V_DAY := NULL; V_MONTH :=NULL;
DBMS_OUTPUT.PUT_LINE('INVALID_NIC');
elsif v_days = 60 then
SELECT CASE WHEN MOD(TO_NUMBER(V_YEAR),400) = 0 THEN 'YES'
WHEN MOD(TO_NUMBER(V_YEAR),100) = 0 THEN 'NO'
WHEN MOD(TO_NUMBER(V_YEAR),4) = 0 THEN 'YES'
ELSE 'NO' END INTO V_LEAP_YR_STATUS
FROM DUAL;
if V_LEAP_YR_STATUS = 'YES' THEN
V_DAY := V_DAYS - 31;
V_MONTH := '02';
ELSE
V_DAY := 01;
V_MONTH := '03';
END IF;
ELSIF (V_DAYS > 335) THEN
V_DAY := V_DAYS - 335;
V_MONTH := '12';
ELSIF (V_DAYS > 305) THEN
V_DAY := V_DAYS - 305;
V_MONTH := '11';
ELSIF (V_DAYS > 274) THEN
V_DAY := V_DAYS - 274;
V_MONTH := '10';
ELSIF (V_DAYS > 244) THEN
V_DAY := V_DAYS - 244;
V_MONTH := '09';
ELSIF (V_DAYS > 213) THEN
V_DAY := V_DAYS - 213;
V_MONTH := '08';
ELSIF (V_DAYS > 182) THEN
V_DAY := V_DAYS - 182;
V_MONTH := '07';
ELSIF (V_DAYS > 152) THEN
V_DAY := V_DAYS - 152;
V_MONTH := '06';
ELSIF (V_DAYS > 121) THEN
V_DAY := V_DAYS - 121;
V_MONTH := '05';
ELSIF (V_DAYS > 91) THEN
V_DAY := V_DAYS - 91;
V_MONTH := '04';
ELSIF (V_DAYS > 60) THEN
V_DAY := V_DAYS - 60;
V_MONTH := '03';
ELSIF (V_DAYS < 32 AND V_DAYS > 0) THEN
V_MONTH := '01';
V_DAY := V_DAYS;
ELSIF (V_DAYS > 31) THEN
V_DAY := V_DAYS - 31;
V_MONTH := '02';
ELSE V_DAY := NULL; V_MONTH :=NULL; V_YEAR := NULL;
END IF;
B_DAY := TO_DATE(TO_CHAR(V_YEAR || V_MONTH||LPAD(V_DAY,2,0)),'YYYYMMDD') ;
--B_DAY := TO_CHAR(V_YEAR || V_MONTH||LPAD(V_DAY,2,0));
RETURN B_DAY;
END;
END;
After run below one;
select GET_BDAY_FRM_NIC_3('943352518V') from dual;