Skip to content
Advertisement

sql query selecting birthday today from number format

Let say i have identification card number in this format

 720901155172

we can convert the number to birthday by using this code

        $data = '720901155172';
        $head = substr($data, 0, 6);

        $year = (int) substr($head, 0, 2);
        $currentYear = date('Y');

        $max = (int) substr($currentYear, 2, 4);

        $byear = $year + ($year > $max ? 1900 : 2000);
        $bmth = (int) substr($head, 2, 2);
        $bday = (int) substr($head, 4, 2);

        $bdate = date('Y-m-d', strtotime(sprintf('%s-%s-%s', $byear, $bmth, $bday)));
        echo $bdate;//output : 1972-09-01

how do i select all user that is having birthday today ? is this possible ?

$sql="SELECT ic FROM users";

Advertisement

Answer

What I understand is first 6 digits of your ic is YYMMDD. If this is not the case please ignore. Accordingly you need this conversion in sql.

    select ic,
    case when substring(ic,1,2) <Substring(YEAR(NOW()),-2) 
    then Concat('20',substring(ic,1,2),'-'  ,substring(ic,3,2),'-', substring(ic,5,2))
    else
     Concat('19',substring(ic,1,2),'-'  ,substring(ic,3,2),'-', substring(ic,5,2))
    end as derived_date
    from Users

please update ‘Substring(YEAR(NOW()),-2)’ with your $max, 2 digit year.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement