To Get a Date into MySql: there must be a better way.

[hugh webster]

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?

tks.

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[Jerry Stuckle]

hugh webster 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?

tks.

Since you're asking about MySQL, I suggest your try a MySQl newsgroup -
such as comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Sandman]

In article <op.s59iljj9fdqzms@mercury>,
"hugh webster" <hw******@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]

[Tony Marston]

Take a look at http://www.tonymarston.net/php-mysql/dateclass.html

--
Tony Marston
http://www.tonymarston.net

"hugh webster" <hw******@bluewin.ch> wrote in message
news:op.s59iljj9fdqzms@mercury...

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?

tks.

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

[Colin McKinnon]

Sandman wrote:

In article <op.s59iljj9fdqzms@mercury>,
"hugh webster" <hw******@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.

Which is very Americanised, and does not have any mechanism for providing a
hint to the underlying code.

Best solution is not to allow users to enter a date into a text field. An
ugly solution is to provide dropdowns for day number, month name and year.
I think the best solution is to use something like Mihai Bazon's jscalendar
(which was one of the main contributing factors to me writing PfP Studio).

HTH

C.

[Scott]

Hugh,

The best solution I have found is to use a javascript calendar to assign
the value to the date field. It's easy for the users, and ensures you
get the date in the format you want. The other option is to have a
month, day, and year dropdown for them to select from.

It's a bit of extra work, but only the first time. If you write the code
in a reusable manner, it will save you countless hours down the road. In
fact, there are several javascript calendars out there which may be
ideal for your situation.

There was mention of using strtotime(), which could work. It basically
returns either a timestamp or false. However, the javascript method
would give you more consistent results, IMO.

Scott

hugh webster 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?

tks.

[Sandman]

In article <fH***************@newsfe3-gui.ntli.net>,
Colin McKinnon
<co**********************@ntlworld.deletemeunlessU RaBot.com> wrote:

Sandman wrote:

In article <op.s59iljj9fdqzms@mercury>,
"hugh webster" <hw******@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.

Which is very Americanised, and does not have any mechanism for providing a
hint to the underlying code.

Best solution is not to allow users to enter a date into a text field. An
ugly solution is to provide dropdowns for day number, month name and year.

I don't know why though. I am using strtotime() for swedish dates. I
just have to have a set of translations for it. In my CMS system,
users enter dates in a text field, and the system warns you if it
can't make a real date out of it.

--
Sandman[.net]

[adrian.price]

I've had a lot of success (and some serious "wow, that's cool"
reactions from users and execs) with a combination of strtotime and
active reponse to the user. I don't know what kind of target platform
you're looking at, but if you're able to expect a fairly recent browser
of your users, try using plain-text date fields with a JavaScript event
that makes an XmlHttpRequest back to your server, to a script (only
needs to be about 5 lines long) that returns the strtotime() of the
input text. It then replaces what the user typed into the date field
with the response from the server. So, for example, they type "next
tuesday", hit tab, the field fills out the full date and time, and they
can look at it and see that it's correctly interpretted their
plain-english date.

Also, as far as storing dates in MySQL itself - I've gotten in the
(admittedly somewhat odd) habit of storing all dates in the DB as plain
integer columns holding a UNIX timestamp. It may seem odd, but it
works, and it's what I'm used to, because I learned PHP/MySQL from
modding phpBB.

Hope that helps!!

[Sandman]

In article <11**********************@i39g2000cwa.googlegroups .com>,
"ad**********@gmail.com" <ad**********@gmail.com> wrote:

I've had a lot of success (and some serious "wow, that's cool"
reactions from users and execs) with a combination of strtotime and
active reponse to the user. I don't know what kind of target platform
you're looking at, but if you're able to expect a fairly recent browser
of your users, try using plain-text date fields with a JavaScript event
that makes an XmlHttpRequest back to your server, to a script (only
needs to be about 5 lines long) that returns the strtotime() of the
input text. It then replaces what the user typed into the date field
with the response from the server. So, for example, they type "next
tuesday", hit tab, the field fills out the full date and time, and they
can look at it and see that it's correctly interpretted their
plain-english date.
That's exactly how I do it.
Also, as far as storing dates in MySQL itself - I've gotten in the
(admittedly somewhat odd) habit of storing all dates in the DB as plain
integer columns holding a UNIX timestamp. It may seem odd, but it
works, and it's what I'm used to, because I learned PHP/MySQL from
modding phpBB.

But it's a killer for complicated date queries on huge tables.


--
Sandman[.net]