From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | David Rickard <David(dot)Rickard(at)GTScompanies(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org, Judith Strawser <judiths(at)GTScompanies(dot)com> |
Subject: | Re: Date Formats and Conversions |
Date: | 2003-10-16 17:45:25 |
Message-ID: | 20031016174525.GF28009@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Thu, Oct 16, 2003 at 11:00:47 -0700,
David Rickard <David(dot)Rickard(at)GTScompanies(dot)com> wrote:
> We have a intranet site we're converting to PostgreSQL; there are numerous
> pages where users have to enter dates (for search ranges, hire dates etc);
> the dates are assumed to be in MM/DD/YY format in the code (using
> TO_DATE(<variable>, 'MM/DD/YY')--but users being users, sometimes dates get
> entered as MM/DD/YYYY--and then PostgreSQL only uses the first 2 digits of
> the year (resulting in e.g., a hire date being entered as '10/10/2003' and
> stored as '2020-10-10'); we could handle this in code--truncating long
> dates or swapping date-conversion formats--before executing the SQL
> statement; but is there a way to tweak PostgreSQL to be more flexible on
> converting date values?
I don't think you need to use to_date at all. You should be able to just
insert the string they entered. Even casting from text to date seems to
work OK.
> Alternatively, there are screens where date fields are optional (e.g.,
> termination date); and if we do an INSERT/UPDATE with a null value in such
> a field [e.g., SET <date-field> = TO_DATE('','MM/DD/YY'], then PostgreSQL
> is spontaneously generating random dates for the field; again, we could add
> conditional logic to omit the field from the INSERT/UPDATE when the input
> is null--but is there another way to handle this?
'' is not NULL. You can use the keyword NULL (and don't put quotes around it)
instead of a quoted string with the date.
From | Date | Subject | |
---|---|---|---|
Next Message | David Rickard | 2003-10-16 18:00:47 | Date Formats and Conversions |
Previous Message | Bruno Wolff III | 2003-10-16 17:40:33 | Re: Access Privileges |