Thread: Date Formats and Conversions
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?
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?
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?
--
David Rickard
Software Engineer
The GTS Companies
A TechBooks Company
----------------------------------------------------------------------------------
The GTS Companies:
GTS Publishing Services, GTS Graphics, GTS Innova:
Your Single-Source Solution!
Los Angeles CA * York, PA * Boston MA * New Delhi, India
----------------------------------------------------------------------------------
David.Rickard@GTSCompanies.com
Visit us on the World Wide Web
http://www.gtscompanies.com
5650 Jillson St., Los Angeles, CA 90040
(323) 888-8889 x331
(323) 888-1849 [fax]
On Thu, Oct 16, 2003 at 11:00:47 -0700, David Rickard <David.Rickard@GTScompanies.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.