How to find two records sharing the same Month and Day of birth in SQLITE3 -

i have table person , in have name , birthday.

for example, have table called person

insert person values ( 'alice', '1986-10-10'); insert person values ( 'kate', '1992-10-10'); 

i type in query produce alice , kate ( query displays entries share month , day, year doesn't matter). birthday used store date , used date data type.

i have other values different birthdates, can't seem figure out query produce alice , kate (because share month , day of birthday)

this have far, not producing anything:

select name person birthday '_____xx__xx'; 

your query work (with corrected pattern), need ensure dates in correct format. documentation:

a time string can in of following formats:  1.  yyyy-mm-dd  ... 

so need use format dates. "1992-3-3" not recognized date; need use "1992-03-03". or can use julian dates makes arithmetic convenient reading things little harder without use of date/time functions.

if use correct iso date format, clause work, or use date/time functions extract piece of date., e.g.,

select name, strftime('%m-%d',birthday) person; alice|10-10 kate|10-10 

so if wanted group term:

select strftime('%m-%d',birthday) mday, count(*) person group mday; 

or find distinct values, or whatever prefer.

for example, can list of people , people sharing birthdays them joining person itself:

select p1.name, p2.name, strftime('%m-%d', p1.birthday)  person p1 left outer join person p2   on (strftime('%m-%d',p1.birthday) = strftime('%m-%d',p2.birthday) , p1.rowid <> p2.rowid) ; 

that is, join table using extracted month-day , exclude joining row itself. using left outer join includes people don't share birthday anyone.