Thread: Evidently no support for the mmddyyyy date format

Today I tried every permutation of the DateStyle parameter I could find, and
still cannot get PostgreSQL 8.3 to accept dates in the format mmddyyyy.  I tried
setting this in the postgresql.conf file, and also in psql using "set datestyle
to <style>".  So, am I correct to conclude that it is not possible to configure
to accept dates on the mmddyyyy format?  I know I can probably use the to_date
function, but this would involve changing a multitude of queries, which is what
I'm trying to avoid.

-Thanks

Bernard Barton <bfb21@comcast.net> writes:
> Today I tried every permutation of the DateStyle parameter I could find, and
> still cannot get PostgreSQL 8.3 to accept dates in the format mmddyyyy.  I tried
> setting this in the postgresql.conf file, and also in psql using "set datestyle
> to <style>".  So, am I correct to conclude that it is not possible to configure
> to accept dates on the mmddyyyy format?

If you mean eightdigitswithoutanypunctuation, I think that's correct.

            regards, tom lane

On 09/04/2009 23:56, Bernard Barton wrote:
> Today I tried every permutation of the DateStyle parameter I could find, and
> still cannot get PostgreSQL 8.3 to accept dates in the format mmddyyyy.  I tried

How exactly are you sending these values to the database? Straight SQL,
or some other mechanism? Can you show us some examples?

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Hello

use to_date function, please.

postgres=# select to_date('10122008','DDMMYYYY');
  to_date
------------
 2008-12-10
(1 row)

Time: 1,152 ms
postgres=#

regards
Pavel Stehule

2009/4/10 Raymond O'Donnell <rod@iol.ie>:
> On 09/04/2009 23:56, Bernard Barton wrote:
>> Today I tried every permutation of the DateStyle parameter I could find, and
>> still cannot get PostgreSQL 8.3 to accept dates in the format mmddyyyy.  I tried
>
> How exactly are you sending these values to the database? Straight SQL,
> or some other mechanism? Can you show us some examples?
>
> Ray.
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

2009/4/10  <bfb21@comcast.net>:
> Yes, I mentioned that I could use the to_date function, but as I said, that
> would involve a LOT of changes to a LOT of source code, which I'm trying to
> avoid.
>

other solution is custom datatype. It isn't too much work, but it is
coding in C.

regards
Pavel Stehule


> -Thanks
>
>
>
> ----- Original Message -----
> From: "Pavel Stehule" <pavel.stehule@gmail.com>
> To: rod@iol.ie
> Cc: "Bernard Barton" <bfb21@comcast.net>, pgsql-general@postgresql.org
> Sent: Friday, April 10, 2009 5:40:21 AM GMT -05:00 US/Canada Eastern
> Subject: Re: [GENERAL] Evidently no support for the mmddyyyy date format
>
> Hello
>
> use to_date function, please.
>
> postgres=# select to_date('10122008','DDMMYYYY');
>   to_date
> ------------
>  2008-12-10
> (1 row)
>
> Time: 1,152 ms
> postgres=#
>
> regards
> Pavel Stehule
>
> 2009/4/10 Raymond O'Donnell <rod@iol.ie>:
>> On 09/04/2009 23:56, Bernard Barton wrote:
>>> Today I tried every permutation of the DateStyle parameter I could find,
>>> and
>>> still cannot get PostgreSQL 8.3 to accept dates in the format mmddyyyy.
>>>  I tried
>>
>> How exactly are you sending these values to the database? Straight SQL,
>> or some other mechanism? Can you show us some examples?
>>
>> Ray.
>>
>> ------------------------------------------------------------------
>> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
>> rod@iol.ie
>> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
>> ------------------------------------------------------------------
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>

Pavel Stehule wrote:
> 2009/4/10  <bfb21@comcast.net>:
> > Yes, I mentioned that I could use the to_date function, but as I said, that
> > would involve a LOT of changes to a LOT of source code, which I'm trying to
> > avoid.
> >
>
> other solution is custom datatype. It isn't too much work, but it is
> coding in C.

Yep, that was my first idea too.  You would copy an existing data type,
and modify just the _input_ routine to handle input with no delimiters.
I would simply add the delimiters and pass the string to the original
input function;  it really isn't that much work.  The only downside is
that you have to create/use a custom data type for this.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

----- bfb21@comcast.net wrote:

> This is embedded SQL in a .pgc file. You can see the "c_docket_date
> between :date1 and :date2" line in the select statement, which is
> where the dates are porcessed. If I pass a date in the mm-dd-yyyy
> format it works. However, the application I'm porting is all based on
> dates in the mmddyyyy format.
>
> I'm 99% cerain that PostgreSQL will NOT support dates in the mmddyyyy
> format, unless you use the to_date function, which I'm trying to
> avoid.
>
>
> select
> c_jnum_prefix, c_jnum_seq, c_jnum_year, c_jnum_suffix, c_jnum_venue
> ,c_actkey, c_disp_cd
> into
> :prfx, :seq, :yr, :sfx, :ven, :actkey, :disp
> from
> c_records
> where
> c_jnum_prefix = :prfx
> and
> c_jnum_seq between :seq1 and :seq2
> and
> c_jnum_venue = :ven
> and
> c_docket_date between :date1 and :date2
> order by
> c_jnum_prefix,c_jnum_seq,c_jnum_year,
> c_jnum_suffix,c_jnum_venue;
>
>

I might be missing something, but could you not preprocess the :date1 and :date2 variables before passing them to the
SQLcode above. 


Adrian Klaver
aklaver@comcast.net