18 January 2038

[johnplank]

I am working on a project to put a local church burial records onto the web.

We have used php and MYSql.

Our problem is that dates between approx 01 January 1875 and 31 December 1899 which have be input are displayed in searches as 18 January 2038 (more specifically 18/01/2038).

I realise that this date is significant and that the web servers are unix.

When using a mysql browser I can see that the dates are stored looking correct, but cannot figure out how to get them to display correctly in php.

Does anyone have any suggestions please.

[guelphdad]

input your dates in the format yyyymmdd and make the column type DATE. Use DATE_FORMAT (see the mysql manual for that) to display your dates as you grab them out of mysql. Don't bother trying to format them with php.

See the article in my links below if you have your dates in varchar/char format. It will tell you how to swap them around to the proper date type.

[johnplank]

Thanks for the suggestion, however, the field is already a date field. It is only dates in the range quoted in my first posting that give problems. Are there any other suggestions?

[guelphdad]

if it is a date field it can hold dates between 1000 A.D. and 9999 A.D. so you may not be entering them correctly or something. Can you look at the data directly in mysql? Perhaps the issue is you are trying to format them in PHP and that is throwing things off?

[johnplank]

THose dates are well within the needed range, the earliest that anyone was buried in our churchyard was 1845.

The code to do the display of the date of burial is:

<?php if (!isset($x_DateBurial)) { echo "<FONT color=FF0000>Unknown</FONT>"; } else { echo FormatDateTime($x_DateBurial,7); } ?>

If I change the else bit to echo $x_DateBurial then the date is correct (displayed as ccyy-mm-dd) so it looks as though your suggestion that the PHP formatting is somehow unpsetting the display.

I guess that somehow I have to manipulate $x_DateBurial into a new variable so that it displays as the average person in England would like to see it, ie dd/mm/ccyy. Can you suggest an easy way of doing this?

[guelphdad]

Use DATE_FORMAT when you are bringing the values out of your database, don't use PHP to format them only to display your results from your database.

select
foo,
bar,
qux,
date_format(datecolumn,'%d/%m/%Y') as BritishDate
from yourtable

[johnplank]

Many thanks, all working now.