Thread: date format

The SQL 92 spec is quite specific that there is an exact order for date
and it is yyyymmdd period, and that these are constrained by the rules of
a well formed gregorian calendar (i.e. no 20020231 allowed)

Seeing as how putting in 2002-22-04 and 2002-04-22 on my box results in
two inputs of april 22nd 2002, it's obvioue postgresql is trying to make
data fit that should result in a constraint violation.

I found this on p 110-112 or so of the SQL92 draft in text that's so
common.  it's section 6.1 or so.

On Thu, 2003-04-03 at 00:59, scott.marlowe wrote:
> The SQL 92 spec is quite specific that there is an exact order for date
> and it is yyyymmdd period, and that these are constrained by the rules of
> a well formed gregorian calendar (i.e. no 20020231 allowed)
>
> Seeing as how putting in 2002-22-04 and 2002-04-22 on my box results in
> two inputs of april 22nd 2002, it's obvioue postgresql is trying to make
> data fit that should result in a constraint violation.

I can confirm that that inserting dates via JSP + the JDBC driver into
Postgresql does exactly this.

If you want a nice interface that inserts dates in the DD-MM-YYYY format
you will have to write a nice little Javascript function that puts the
date back into the right order before inserting into the database.

SELECT to_char(dateexpofin, 'DD-MM-YYYY') as end, dateexpofin
ORDER BY dateexpofin DESC

will write out the date in "normal" European order and put the latest
date on top of the list.

I was stuck on dates a while back.

Cheers

Tony Grant
--
www.tgds.net Library management software toolkit,
redhat linux on Sony Vaio C1XD,
Dreamweaver MX with Tomcat and PostgreSQL