List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 25 2002 7:40pm
Subject:Re: specifying date format for search
View as plain text  
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.
>>  >
>>  >Example:
>>  >select create_date from what_ever where create_date >
>>  >date_format('03/01/2002','%m/%d/%Y')
>>
>>  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).
Thread
specifying date format for searchwalt25 Jun
  • Re: specifying date format for searchPaul DuBois25 Jun
    • Re: specifying date format for searchwalt25 Jun
      • Re: specifying date format for searchPaul DuBois25 Jun