Date help

kpowning

How can I convert a date as: 1/4/2006 in a mysql field to be ordered by desc or asc? I have a field for example called dates that is supplied by a datafeed with the format shown above. I need to be able to sortby this field is there anyway?

Weedpacket

Store the date properly (i.e., as a date; you may have to convert it before you insert it and why doesn't the datafeed use a sensible format?).

Then ORDER BY ASC/DESC would work without any trouble.

kpowning

How can I convert it before then? Even though they are still passing it this way? Thanks in advance.

NogDog

[man]strtotime/man can convert most standard formats to a UNIX timestamp integer, which you can then pass to [man]date/man to get the desired format ("Y-m-d" for MySQL). Generally the surest way to get the user to enter the data in a usable "format" is to have separate fields for month, day, and year, and then "glue" them together, perhaps with [man]mktime/man or else the aforementioned strtotime(). You could also use [man]sprintf/man:

<?php
$month = (int) $_POST['month'];
$day = (int) $_POST['day'];
$year = (int) $_POST['year'];
if(checkdate($month, $day, $year) == false)
{
   // error: invalid date.
}
else
{
   $dbDate = sprintf("%04d-%02d-%02d", $year, $month, $day);
}

But if you just want/need to allow the user to enter a single date field:

$date = strtotime($_POST['date']);
if($date == 0)
{
   // error: invalid date.
}
else
{
   $dbDate = date('Y-m-d', $date);
}

Weedpacket

Of course, I read that date as "1st of April, 2006". I think strtotime would read it as January 4th. Lemme check... "echo date('Y-m-d', strtotime('1/4/2006'));" ... yes, it does. So you probably wouldn't need to use [man]explode[/man] or [man]sscanf[/man] to read and parse it for its components.

kpowning wrote:
How can I convert it before then? Even though they are still passing it this way? Thanks in advance.

They pass it, you convert it, then you store it.

Why doesn't the feed use a sensible format? There are international standards for these things.