Posted by Sandman on 03/11/06 21:42

In article <op.s59iljj9fdqzms@mercury>,
"hugh webster" <hwebster@bluewin.ch> wrote:

> MySql seems to only accept dates as 'yyyy-mm-dd'. How do I do that when
> the user might input dd/mm/yy, or d.m.yyyy (I'm in Europe)? I know I can
> do sscanf, or explode to rebuild the date string - but there must be a
> better way. Is there a FAQ somwhere?

Well, there is a function - strtotime(), which will take plain english
and try to turn it into a timestamp.

For instance:

print date("Y-m-d", strtotime("Next tuesday"));

Will print:

2006-03-14

So, this won't really help with date formats like "2.7.2006", but it's
a base to build a more powerful "string to date" function on. You may
need to take your usual date formats and convert them into something
strtotime() understands. I've built a function that will turn "next
monday at 18" into "2006-03-13 18:00" and so on, by tweaking natural
english into time formats strtotime() understands.

For your specific examples, something like this could work:

# if it's in format 2.3.2006
if (preg_match("/^(\d{1,2})\.(\d{1,2})\.(\d{4})$/", $date, $m)){
$newdate = sprintf("%d-%02d-%02d", $m[3], $m[2], $m[1]);
}

# if it's in format 2/3/2006
if (preg_match("!^(\d{1,2})/(\d{1,2})/(\d{4})$!", $date, $m)){
$newdate = sprintf("%d-%02d-%02d", $m[3], $m[2], $m[1]);
}


The above will convert 2.3.2006 and 2/3/2006 to 2006-03-02.

Another option is to change the date format in MySQL, but 2006-01-01
is ISO standard, so I'd recommend working on the other end. That's
what I do.




--
Sandman[.net]

[Back to original message]