At 15:21 -0400 6/25/02, walt wrote:
>On Tuesday 25 June 2002 01:14 pm, Paul DuBois wrote:
>> At 11:08 -0400 6/25/02, walt wrote:
>> >Does anyone know if version 4.0 will support formatted date queries.
>> >select create_date from what_ever where create_date >
>> Why not just write the date correctly? That is, what does
>> this gain you?
>> >sql, query
>I've got 110 perl CGIs and another group of C++ programs that are written for
>oracle which allows you to specify the date format for queries, updates, and
>inserts. All users enter dates in mm/dd/yyyy format and changing that is not
>an option. It would save me lots of time and coding not switching between
>mm/dd/yyyy and yyyy-mm-dd. We moving to mysql and many of our queries are
>date based. Here is an example of a query that I would like to make to find
>out how many times we set an appointments for the half hour.
>select count(*) from what_ever where date_time = date_format('30','%i');
Well. If what you're really asking is "how to I change the default
format in which MySQL stores dates?", the answer is "you can't."
MySQL always stores them in ISO format (CCYY-MM-DD). I guess in your
situation I'd write a function that converted dates as users enter them
from mm/dd/yyyy to ISO format and plug that into the scripts. But you'll
still need to rewrite the queries. You can probably apply DATE_FORMAT()
to the columns that you're selecting in some cases (rather than to the
values that you want the columns to match).