Date Problem *RESOLVED*

Within a database table I have an entry for Brian Lara with a date of birth of 1969-05-02.

I go to print it as follows:

$DOB = $myrow["DOB"];
echo date("d M Y",mktime(0, 0, 0, substr($DOB,5,2), substr($DOB,8,2),substr($DOB,0,4)));

and it comes out as 31 Dec 1969.

Any ideas why? I have hundreds of other dates converting fine but it just seems to affect this one :confused:

Then that date must be the only one which is before January 1st 1970, the UNIX epoch. Am I right in saying that you are running PHP on a Windows system? If so then PHP cannot handle any dates before the UNIX epoch.

Here is what I suggest you do. MySql has function which is similar to PHP's date function, but the difference being it can handle any date. Use it inside your query like this:

Code:

---

SELECT DATE_FORMAT(date_field,'%e%D %b %Y') FROM table_name;

---

More info on MySql's date/time functions can be found here:

http://dev.mysql.com/doc/mysql/en/da...functions.html

Thanks for your response visualAd.

My host runs Linux actually. Do you still suggest I use the following?

This is the query I'm using btw:

$sql = " SELECT PlayerIDAuto, PlayerName, DOB, TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB";

This is what the PHP manual says:

Quote:

---

Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer). On Windows this range is limited from 01-01-1970 to 19-01-2038.

---

I am quite sure this is the cause of your problem and its easy to test just change the date to 2/1/1970 and see if it works. So yes, I would suggest you use the mysql function.

Quote:

---

Originally Posted by JamesNZ

This is the query I'm using btw:

$sql = " SELECT PlayerIDAuto, PlayerName, DOB, TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB";

---

So you would change it to this:

Code:

---

SELECT PlayerIDAuto, PlayerName, DATE_FORMAT(DOB,'%e%D %b %Y') DOB, ...

---

Cheers VisualAd - will try that :)

Didn't work I'm afraid, it converted all dates to 31 Dec 1969, not just Brian Lara's. :(

This was what I have now:

$sql = " SELECT PlayerIDAuto, PlayerName, DATE_FORMAT(DOB,'%e%D %b %Y'), TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB";

You are not still using the mktime() function are you? MySql formats the date now, so all you need is this:

PHP Code:

---

$DOB = $myrow["DOB"];
echo ($DOB); 


---

You also need to give the DOB column an alias name:

Code:

---

$sql = " SELECT PlayerIDAuto, PlayerName, DATE_FORMAT(DOB,'%e%D %b %Y') DOB, TopPlayer, Country
    FROM Player
        WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-',
            DAYOFMONTH(DOB))) - TO_DAYS(CURDATE())
            BETWEEN 0 AND 7 ORDER BY DOB";

---

P.s: You can have strings on multiple lines in PHP.

Getting closer I think visualAd thanks.

It now shows:

Brian Lara
01 Dec 2021

Here's the query:

$sql = " SELECT PlayerIDAuto, PlayerName, DATE_FORMAT(DOB,'%e%D %b %Y') DOB, TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB";

$DOB = $myrow["DOB"];
echo date("d M Y",mktime(0, 0, 0, substr($DOB,5,2), substr($DOB,8,2),substr($DOB,0,4)));

Thanks, will change that.

Read post #9, you don't need to use the date() and mktime() functions now.

Still doesn't work :(

I get this now:

Brian Lara
22nd May 1969

when it should be:

2nd May 1969

and these are two other players:

Ashish Nehra
2929th Apr 1979
Stuart Matsikenyeri
33rd May 1983


$sql = " SELECT PlayerIDAuto, PlayerName, DATE_FORMAT(DOB,'%e%D %b %Y') DOB, TopPlayer, Country";
$sql.= " FROM Player";
$sql.= " WHERE TO_DAYS(CONCAT('2005-', MONTH(DOB), '-', DAYOFMONTH(DOB))) - TO_DAYS(CURDATE()) ";
$sql.= " BETWEEN 0 AND 7";
$sql.= " ORDER BY DOB";

$DOB = $myrow["DOB"];

echo $DOB;

My mistake, get rid of the %e. If you have a look at the link I posted in my first reply, you'll see why it was coming out wrong.

Code:

---

DATE_FORMAT(DOB,'%D %b %Y') DOB

---

Fantastic - that did the trick - thanks :cool:

Previously I was using:

$DOBYear = date("Y",mktime(0, 0, 0, substr($DOB,5,2), substr($DOB,8,2),substr($DOB,0,4)));

to get the year out to pass into a formula. How could I get the year out using this method?

The year will always be the last for characters, so you can use the substr() function to get it:

PHP Code:

---

$year = substr($DOB, -4, 4); 


---

Thanks again.

I used $DOBYear = substr($DOB, -4, 4); to get it correctly.

$DOBYear = substr($DOB, -1, 4); only gave me the final digit of the year.

Don't forget to add resolved if all is now well. ;)

My mistake, sorry about that. All done now :)