Skip site navigation (1) Skip section navigation (2)

Date Formats and Conversions

From: David Rickard <David(dot)Rickard(at)GTScompanies(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: Judith Strawser <judiths(at)GTScompanies(dot)com>
Subject: Date Formats and Conversions
Date: 2003-10-16 18:00:47
Message-ID: 5.2.1.1.0.20031016104815.00ac5008@mail.gtscompanies.com (view raw or flat)
Thread:
Lists: pgsql-novice
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?


--

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(dot)Rickard(at)GTSCompanies(dot)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]

Responses

pgsql-novice by date

Next:From: dev o'nullDate: 2003-10-17 10:45:29
Subject: scaleable design for multiple value tuple records
Previous:From: Bruno Wolff IIIDate: 2003-10-16 17:45:25
Subject: Re: Date Formats and Conversions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group