Birthday format identification

[code739]

hi,

i have a table in database that contains birthday column varchar()- i choose varchar
because ecoder could input birthday like
1. 1993-14-03
2. 1993-03-14
3. March 14
4. March 14,1993
5.etc....

Now my problem i want to detect what month is their birthday could some help me?

[AARTI SHRIVAS]

can you post your code for taking birthday data from user
if you are using select box then you can give month name in it and store month by name

[code739]

no,...........such data above are already stored in database
the problem is determining the month of every data as i retrive it to php

like if i have this code

$sql = "select birthday from person";
$query = mysql_query($sql);
$result = mysql_fetch_array($query);
$birthday = $result['birthday']; this could have the value i posted above..

i need some creative idea or code perhaps =) thanks

[AARTI SHRIVAS]

i know above data already in data base but i m asking how you insert that data in data base store months by name in database then you easily determine what month user selected

[pritaeas]

There are some ways to extract the information if your date can be recognized as a specific one. The problem remains with a date like 2013-01-02. There is no way to tell if the month is January or February.

[Biiim]

be impossible to get 100% of the data back, 2012-01-04 and 2012-04-01 both valid dates and no idea which format it was entered as, you could get the obvious ones though, i'd use a combo of substr() and locate()

SELECT SUBSTR('2012-22-07',LOCATE('-',CURDATE())+1,2) AS `second_num`;
SELECT SUBSTR('2012-22-07',LOCATE('-',CURDATE())+4,2) AS `third_num`;

then you can find out the ones above 12 must be the day so the other is the month - the rest are 50 50 chance.

for the worded dates like 'March' i'd use php's strtotime function cause that's pretty good at translating string dates

foreach($data as $row){
    echo $row['date'].' -> '.date("Y-m-d",strtotime($row['date']))."<br/>\r\n";
}

Your problem is the data format for the DB. You should only accept ONE format, typically unix date: Y-m-d.
When an user presents their details, your form design should allow them to submit data in a consistent format, such as from a datepicker or multiple dropdowns. I would recommend the former, as multiple dropdowns can give you false dates, e.g. 1967-02-31, and checking each dropdown in turn is a royal PITA.
However, if you've got yourself into this mess and can't change your DB/form, then use strtotime() and convert to the format of your choice.

Anyway you can search in MySQL: MONTHNAME() = 'February' on certain formats, and LIKE '%February%' for others.
This type of date: 1993-14-03 is nonsense, and I can't see how you'd determine this. It goes back to what I stated earlier - DO NOT allow your users to enter their own formats.

[code739]

thanks everyone