Problem formatting mysql date field

Hello.

I seem to have found a problem with the 'frm' function when processing a mysql 'date' field, but it works okay with 'datetime' field.

For example, my table has a column for 'Date of Birth' which is defined as a sql 'date' type, with data '1984-09-12'.

Using frm='d mmmm yyyy' I get '1 January 1970' displayed.

However, if the column is defined on db as type 'datetime' then the format shows correct date.

I would prefer to just use 'date' (not 'datetime') for this field because time is irrelevant in this case.

In case it matters, I am using the ezSQL plug-in to load the data, and running on PHP 4.3.2.

Is there a way around this or is it a bug?

Thanks for help.

Your code works fine with the data you give.

The error you give is one that usually comes from code that is assuming the input data is in Unix time format (seconds since 1 Jan 1070) = "464673600" for the date you gave.

Add a code snippet to output the raw source data and any intermediate processing steps  directly - no formatting - to make sure the data is what you think and the processing is what you think.

Thanks for fast reply. But I am still confused!!!

The data on mysql table is as follows:

Name = 'Fred' (varchar)
DateOfBirth = '1984-09-12' (date)
RowCreated = '2006-07-17 10:20:04' (datetime)


PHP:

$tbs->MergeBlock('person_block', $db, "SELECT * FROM Person ORDER BY PersonName");
$tbs->Show();


When HTML is with NO formatting:
<tr>
<td>[person_block.Name;block=tr]</td>
<td>[person_block.DateOfBirth]</td>
<td>[person_block.RowCreated]</td>
</tr>

Output shows:
<tr>
<td>Fred</td>
<td>1984-09-12</td>
<td>2006-07-17 10:20:04</td>
</tr>

Above is correct and as expected.

*** BUT ***

If I add formatting to HTML like this:
<tr>
<td>[person_block.Name;block=tr]</td>
<td>[person_block.DateOfBirth;frm='d mmmm yyyy']</td>
<td>[person_block.RowCreated;frm='d mmmm yyyy']</td>
</tr>

Now output shows:
<tr>
<td>Fred</td>
<td>1 January 1970</td>
<td>17 July 2006</td>
</tr>

As you can see, the second date is correct but the first is incorrect, and the only difference is the type of mysql field (date rather than datetime).

Hi,

I have no problem neither.
TBS 2.0.7 or 3.1.1
PHP 4.3.10 or 5.1.2

[person_block.DateOfBirth;frm='d mmmm yyyy'] displays "12 September 1984" without you date comming from a MySQL (4.1.9) DATE field.

Hi,

Thanks, I have discovered problem. In fact my example date '1984-09-12' worked but that should have read '1964-09-12'. The problem was with all dates earlier than 1970 being wrong, and it was my testing was on Windows machine that doesn't recognise negative timestamps.

I uploaded to a Linux server to test and all dates now format correctly.

I hate Windows!!!


Thanks again for help.

tb