pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate format
13+ messages / 12 participants
[nested] [flat]
* date format
@ 2000-05-29 05:37 Carolyn Wong <carolyn@kss.net.au>
0 siblings, 0 replies; 13+ messages in thread
From: Carolyn Wong @ 2000-05-29 05:37 UTC (permalink / raw)
To: pgsql-sql
Hi All
I'm using V6.5.
I need to use the dd/mm/yy format, but the database seems to interpret
it as mm/dd/yy, unless the digig >12, then the date format is
interpreted as dd/mm/yy. Are there any setting required?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Date Format
@ 2000-12-13 10:51 Daniel Hentges <danielh@fockink.ind.br>
2000-12-13 13:45 ` RE: Date Format Francis Solomon <francis@stellison.co.uk>
0 siblings, 1 reply; 13+ messages in thread
From: Daniel Hentges @ 2000-12-13 10:51 UTC (permalink / raw)
To: pgsql-sql
In MS Access is:
SELECT FORMAT([field],'DD/MM/YYYY') AS new name;
How I can make in pgaccess?
Daniel Hentges
Automação
Fockink In. Eletricas Ltda.
Panambi - RS - Brasil
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Date Format
2000-12-13 10:51 Date Format Daniel Hentges <danielh@fockink.ind.br>
@ 2000-12-13 13:45 ` Francis Solomon <francis@stellison.co.uk>
0 siblings, 0 replies; 13+ messages in thread
From: Francis Solomon @ 2000-12-13 13:45 UTC (permalink / raw)
To: Daniel Hentges <danielh@fockink.ind.br>; pgsql-sql
Hi Daniel,
Try this as your query:
SELECT to_char(field, 'DD/MM/YYYY') AS "new name";
Hope this helps
Francis Solomon
>
> In MS Access is:
> SELECT FORMAT([field],'DD/MM/YYYY') AS new name;
> How I can make in pgaccess?
>
> Daniel Hentges
> Automação
> Fockink In. Eletricas Ltda.
> Panambi - RS - Brasil
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* date format
@ 2001-06-01 14:40 Martín Marqués <martin@bugs.unl.edu.ar>
0 siblings, 0 replies; 13+ messages in thread
From: Martín Marqués @ 2001-06-01 14:40 UTC (permalink / raw)
To: pgsql-sql
I was reading the date data type section of the Postgres Manual, and found
this:
Date and time input is accepted in almost any reasonable format, including
ISO-8601, SQL-compatible, traditional Postgres, and others. The ordering of
month and day in date input can be ambiguous, therefore a setting exists to
specify how it should be interpreted in ambiguous cases. The command SET
DateStyle TO 'US' or SET DateStyle TO 'NonEuropean' specifies the variant
"month before day", the command SET DateStyle TO 'European' sets the variant
"day before month". The ISO style is the default but this default can be
changed at compile time or at run time.
Now, normally work with day/month/year format, and work normally with PHP, so
do we have to set the datestyle variable each time we open a connection? Can
this be the default as it says in the manuals and how?
Saludos... :-)
--
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques | mmarques@unl.edu.ar
Programador, Administrador | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 13+ messages in thread
* Date format
@ 2002-04-11 07:00 cristi <cristi@dmhi.ct.ro>
0 siblings, 0 replies; 13+ messages in thread
From: cristi @ 2002-04-11 07:00 UTC (permalink / raw)
To: pgsql-sql
Hi,
I try to set up PG7.1.3
The date format now is yyyy-mm-dd
How can I start it in date format mm-dd-yyyy?
in posgresql file I have:
.......-p /usr/bin/postmaster start -o -i > dev/null 2>...........
Thanks in advance!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Date format
@ 2002-04-11 07:04 cristi69 <cristiv69@yahoo.com>
2002-04-11 07:20 ` Re: Date format Tubagus Nizomi <nizomi@dnet.net.id>
0 siblings, 1 reply; 13+ messages in thread
From: cristi69 @ 2002-04-11 07:04 UTC (permalink / raw)
To: pgsql-sql
Hi,
I try to set up PG7.1.3
The date format now is yyyy-mm-dd
How can I start it in date format mm-dd-yyyy?
in posgresql file I have:
.......-p /usr/bin/postmaster start -o -i > dev/null 2>...........
Thanks in advance!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Date format
2002-04-11 07:04 Date format cristi69 <cristiv69@yahoo.com>
@ 2002-04-11 07:20 ` Tubagus Nizomi <nizomi@dnet.net.id>
0 siblings, 0 replies; 13+ messages in thread
From: Tubagus Nizomi @ 2002-04-11 07:20 UTC (permalink / raw)
To: pgsql-sql
you can try
psql>SET DATESTYLE TO ISO/SQL/Postgres
ISO yyyy-mm-dd
SQL mm/dd/yyyy
Postgres Wed Dec 17 07:37:16 1997
Nizomi
----- Original Message -----
From: cristi69
To: pgsql-sql@postgresql.org
Sent: Thursday, April 11, 2002 2:04 PM
Subject: [SQL] Date format
Hi,
I try to set up PG7.1.3
The date format now is yyyy-mm-dd
How can I start it in date format mm-dd-yyyy?
in posgresql file I have:
.......-p /usr/bin/postmaster start -o -i > dev/null 2>...........
Thanks in advance!
^ permalink raw reply [nested|flat] 13+ messages in thread
* date format
@ 2008-01-24 08:06 iuri de araujo sampaio <iuri.sampaio@gmail.com>
2008-01-24 08:21 ` Re: date format A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-24 15:37 ` Re: date format Adrian Klaver <aklaver@comcast.net>
2008-01-24 15:41 ` Re: date format Scott Marlowe <scott.marlowe@gmail.com>
2008-01-25 14:15 ` Re: date format Frank Bax <fbax@sympatico.ca>
0 siblings, 4 replies; 13+ messages in thread
From: iuri de araujo sampaio @ 2008-01-24 08:06 UTC (permalink / raw)
To: pgsql-sql
hi,
how to change the default format for type date?
I have created a field on a table:
##
create table tbl_inventory (
item_id integer constraint c_pk primary key,
I. purchase_date date,
II. fabrication_date date,
III. expiration_date date
);
##
the error i got is:
##
Database operation "dml" failed
(exception ERROR, "ERROR: invalid input syntax for type date: "2008 7 22 {} {} {} {DD MONTH YYYY}"
")
##
I am concerned the input syntax for type date is YYYY-MM-DD.
as default, postgresql is set to accept the format DD-MM-YYYY for type
date.
so far, i am not able to change the input format and so I need to change
the postgresql type date dafault format.
best,
iuri
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: date format
2008-01-24 08:06 date format iuri de araujo sampaio <iuri.sampaio@gmail.com>
@ 2008-01-24 08:21 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
3 siblings, 0 replies; 13+ messages in thread
From: A. Kretschmer @ 2008-01-24 08:21 UTC (permalink / raw)
To: pgsql-sql
am Thu, dem 24.01.2008, um 5:06:58 -0300 mailte iuri de araujo sampaio folgendes:
> hi,
>
> how to change the default format for type date?
You can change datestyle, a simple example:
test=# select '30.12.2007'::date;
ERROR: date/time field value out of range: "30.12.2007"
HINT: Perhaps you need a different "datestyle" setting.
test=!# rollback;
ROLLBACK
test=# set datestyle=german;
SET
test=*# select '30.12.2007'::date;
date
------------
30.12.2007
(1 row)
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: date format
2008-01-24 08:06 date format iuri de araujo sampaio <iuri.sampaio@gmail.com>
@ 2008-01-24 15:37 ` Adrian Klaver <aklaver@comcast.net>
3 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2008-01-24 15:37 UTC (permalink / raw)
To: pgsql-sql; iuri.sampaio@computer.org
On Thursday 24 January 2008 12:06 am, iuri de araujo sampaio wrote:
> hi,
>
> how to change the default format for type date?
> I have created a field on a table:
>
> ##
> create table tbl_inventory (
> item_id integer constraint c_pk primary key,
> I. purchase_date date,
> II. fabrication_date date,
> III. expiration_date date
> );
> ##
>
> the error i got is:
>
> ##
> Database operation "dml" failed
> (exception ERROR, "ERROR: invalid input syntax for type date: "2008 7 22
> {} {} {} {DD MONTH YYYY}" ")
> ##
If this : "2008 7 22 {} {} {} {DD MONTH YYYY}" is indeed the string you are
trying to input then it will not work. See-
http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-DATETIME-INPUT
for valid input types.
>
> I am concerned the input syntax for type date is YYYY-MM-DD.
> as default, postgresql is set to accept the format DD-MM-YYYY for type
> date.
>
> so far, i am not able to change the input format and so I need to change
> the postgresql type date dafault format.
>
> best,
> iuri
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: date format
2008-01-24 08:06 date format iuri de araujo sampaio <iuri.sampaio@gmail.com>
@ 2008-01-24 15:41 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-24 20:59 ` Re: date format Scott Marlowe <scott.marlowe@gmail.com>
3 siblings, 1 reply; 13+ messages in thread
From: Scott Marlowe @ 2008-01-24 15:41 UTC (permalink / raw)
To: iuri.sampaio@computer.org; +Cc: pgsql-sql
On Jan 24, 2008 2:06 AM, iuri de araujo sampaio <iuri.sampaio@gmail.com> wrote:
> hi,
>
> how to change the default format for type date?
> I have created a field on a table:
>
> ##
> create table tbl_inventory (
> item_id integer constraint c_pk primary key,
> I. purchase_date date,
> II. fabrication_date date,
> III. expiration_date date
> );
> ##
>
> the error i got is:
>
> ##
> Database operation "dml" failed
> (exception ERROR, "ERROR: invalid input syntax for type date: "2008 7 22 {} {} {} {DD MONTH YYYY}"
> ")
> ##
Can you give us a statement by statement example of what you're doing?
Are you actually trying to insert the string '2008 7 22 {} {} {} {DD
MONTH YYYY}' as a date?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: date format
2008-01-24 08:06 date format iuri de araujo sampaio <iuri.sampaio@gmail.com>
2008-01-24 15:41 ` Re: date format Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-01-24 20:59 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Scott Marlowe @ 2008-01-24 20:59 UTC (permalink / raw)
To: iuri.sampaio@computer.org; +Cc: pgsql-sql
Please keep replies on list. Others might have input that will help.
On Jan 24, 2008 11:24 AM, iuri de araujo sampaio <iuri.sampaio@gmail.com> wrote:
> Yes, I am trying to insert the string ´2008 7 22´ as a date.
> and i can´t change the input format. Is that a edit the default format
> type date, in order to postgresql accept thi
> s input?
Are you trying to insert '2008 7 22' or '2008 7 22 {} {} {} {DD MONTH
YYYY}' as a date?
If I try this:
create table test (dt date);
insert into test values ('2008 7 22');
INSERT 0 1
select * from test;
dt
------------
2008-07-22
(1 row)
It works.
However, that other string is most certainly NOT a date.
Note I'm running pgsql 8.2.6 on ubuntu, just fyi.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: date format
2008-01-24 08:06 date format iuri de araujo sampaio <iuri.sampaio@gmail.com>
@ 2008-01-25 14:15 ` Frank Bax <fbax@sympatico.ca>
3 siblings, 0 replies; 13+ messages in thread
From: Frank Bax @ 2008-01-25 14:15 UTC (permalink / raw)
To: iuri.sampaio@computer.org; pgsql-sql
iuri de araujo sampaio wrote:
> hi,
>
> how to change the default format for type date?
> I have created a field on a table:
>
> ##
> create table tbl_inventory (
> item_id integer constraint c_pk primary key,
> I. purchase_date date,
> II. fabrication_date date,
> III. expiration_date date
> );
> ##
>
> the error i got is:
>
> ##
> Database operation "dml" failed
> (exception ERROR, "ERROR: invalid input syntax for type date: "2008 7 22 {} {} {} {DD MONTH YYYY}"
> ")
> ##
Assuming that your import code has something similar to:
insert into tbl_inventory(item_id,purchase_date)
values(1,"2008 7 22 {} {} {} {DD MONTH YYYY}")
I would change it to something like:
insert into tbl_inventory(item_id,purchase_date)
values(1,regexp_replace('2008 7 22 {} {} {} {DD MONTH YYYY}','{.+}
*','','g')::date);
Frank
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2008-01-25 14:15 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-29 05:37 date format Carolyn Wong <carolyn@kss.net.au>
2000-12-13 10:51 Date Format Daniel Hentges <danielh@fockink.ind.br>
2000-12-13 13:45 ` RE: Date Format Francis Solomon <francis@stellison.co.uk>
2001-06-01 14:40 date format Martín Marqués <martin@bugs.unl.edu.ar>
2002-04-11 07:00 Date format cristi <cristi@dmhi.ct.ro>
2002-04-11 07:04 Date format cristi69 <cristiv69@yahoo.com>
2002-04-11 07:20 ` Re: Date format Tubagus Nizomi <nizomi@dnet.net.id>
2008-01-24 08:06 date format iuri de araujo sampaio <iuri.sampaio@gmail.com>
2008-01-24 08:21 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-01-24 15:37 ` Adrian Klaver <aklaver@comcast.net>
2008-01-24 15:41 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-24 20:59 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-25 14:15 ` Frank Bax <fbax@sympatico.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox