sql query selecting birthday today from number format

Question

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";

Possible duplicate of :- [Use MySQL to determine whether today is a user's birthday](https://stackoverflow.com/questions/2218378/use-mysql-to-determine-whether-today-is-a-users-birthday) — Alive to die - Anant, Jan 31 '20 at 04:41
simple table uid mediumint(8) ic varchar (25) name varchar (128) — darrenx, Jan 31 '20 at 04:44
What is the date format, is it number of seconds from some year? — Fact, Jan 31 '20 at 04:52
When you say `how do i select all user that is having birthday today` the "today" refers to the current timestamp ? or is it the derived timestamp i.e `1972-09-01` And what is the Birthday Column Name? — Fact, Jan 31 '20 at 04:58
It's just a where caluse .. am I missing someting `$sql="SELECT name FROM users where ic=derived_date" — Fact, Jan 31 '20 at 05:36

Cray · Answer 1 · 2020-01-31T06:47:37.957

Since the birthday that you need to look at is on positions 2-5 in the ic string you can use REGEX to match it with today's date.

SELECT * FROM users WHERE ic REGEXP '^[0-9]{2}0901[0-9]{6}$';

Assuming each ic is a fixed length of 12 numbers this will match a string that:

starts with 2 numbers between 0-9
matches input date exactly
ends with 6 numbers between 0-9

Today's date part of the REGEX you will have to insert into the query dynamically with PHP, so the code would look (depending on what framework you are using) something like:

$db->get_results("SELECT * FROM users WHERE ic REGEXP '^[0-9]{2}%d[0-9]{6}$'", $date);

score 0 · Accepted Answer · answered Jan 31 '20 at 07:06

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.

sql query selecting birthday today from number format

2 Answers2