Evidently no support for the mmddyyyy date format

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>:

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.

-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>:

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;

----- Original Message -----
From: "Raymond O'Donnell" <rod@iol.ie>
To: "Bernard Barton" <bfb21@comcast.net>
Cc: pgsql-general@postgresql.org
Sent: Friday, April 10, 2009 5:31:45 AM GMT -05:00 US/Canada Eastern
Subject: Re: [GENERAL] Evidently no support for the mmddyyyy date format

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
------------------------------------------------------------------

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

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 SQL code above.

Adrian Klaver
aklaver@comcast.net