Thread

1. to_date problems (Re: Favor for Postgres User at WSI)

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-07T23:42:34Z

   Kate Collins <klcollins@wsicorp.com> writes:
   >> In other words it is defaulting to the year 0 (actually year 1 BC, since
   >> there is no year 0) instead of 2000.
   
   Hmm, you're right:
   
   regression=# select to_date( '001112', 'YYMMDD');
       to_date
   ---------------
    0001-11-12 BC
   (1 row)
   
   >> Now I run the equivalent select statement on Oracle, and I get:
   >> select to_char( to_date( '001112', 'YYMMDD'), 'YYYYMMDD') from dual;
   >> TO_CHAR(TO_DATE('001112','YYMMDD'),'YYYYMMDD')
   >> ---------------------------------------------------------------------------
   >> 20001112
   >> 
   >> Which is what I expect.
   >> 
   >> Is "YY" suppose to default to the current century or is this an
   >> Oracle'ism?
   
   I dunno whether there is any actual spec for to_date(), but I do agree
   that if you've specified a 2-digit YY format, something 2000-centric
   would be more useful than the current behavior.
   
   It doesn't seem to be doing anything particularly sensible with a
   4-digit date, either:
   
   regression=# select to_date( '00001112', 'YYYYMMDD');
     to_date
   ------------
    1112-11-12
   (1 row)
   
   This case I *would* have expected to produce 1 BC, but nope...
   
   >> BTW, on postgres, when I try:
   >> select to_char( to_date( '20001112', 'YYYYMMDD'), 'YYYYMMDD');
   >> I get the error:  "ERROR:  Unable to convert timestamp to date"
   
   That seems broken in current sources, too:
   
   regression=# select to_date( '20001112', 'YYYYMMDD');
   ERROR:  Unable to convert timestamp to date
   
   Looks like you've rooted out a number of problems in to_date (which
   in fairness is new-in-7.0 code).  I've cc'd this to to_date's author,
   whom I hope will find a fix for 7.1.
   
   BTW, direct conversion to timestamp does something sensible in all
   these cases:
   
   regression=# select  '001112'::timestamp;
           ?column?
   ------------------------
    2000-11-12 00:00:00-05
   (1 row)
   
   regression=# select  '00001112'::timestamp;
      ?column?
   ---------------
    0001-11-12 BC
   (1 row)
   
   regression=# select  '20001112'::timestamp;
           ?column?
   ------------------------
    2000-11-12 00:00:00-05
   (1 row)
   
   so it doesn't seem to be the fault of the underlying timestamp or
   date datatypes ...
   
   			regards, tom lane
   
   

2. Re: to_date problems (Re: Favor for Postgres User at WSI)

   Karel Zak <zakkr@zf.jcu.cz> — 2000-11-08T10:11:39Z

   On Tue, 7 Nov 2000, Tom Lane wrote:
   
   > Kate Collins <klcollins@wsicorp.com> writes:
   > >> In other words it is defaulting to the year 0 (actually year 1 BC, since
   > >> there is no year 0) instead of 2000.
   > 
   > Hmm, you're right:
   > 
   > regression=# select to_date( '001112', 'YYMMDD');
   >     to_date
   > ---------------
   >  0001-11-12 BC
   > (1 row)
   >
   > >> Now I run the equivalent select statement on Oracle, and I get:
   > >> select to_char( to_date( '001112', 'YYMMDD'), 'YYYYMMDD') from dual;
   > >> TO_CHAR(TO_DATE('001112','YYMMDD'),'YYYYMMDD')
   > >> ---------------------------------------------------------------------------
   > >> 20001112
   > >> 
   > >> Which is what I expect.
   > >> 
   > >> Is "YY" suppose to default to the current century or is this an
   > >> Oracle'ism?
   > 
   > I dunno whether there is any actual spec for to_date(), but I do agree
   > that if you've specified a 2-digit YY format, something 2000-centric
   > would be more useful than the current behavior.
   > 
   > It doesn't seem to be doing anything particularly sensible with a
   > 4-digit date, either:
   > 
   > regression=# select to_date( '00001112', 'YYYYMMDD');
   >   to_date
   > ------------
   >  1112-11-12
   > (1 row)
   > 
   > This case I *would* have expected to produce 1 BC, but nope...
   
    Where is *guarantee* that the year is 4-digits?!
   
   test=# select '123456-11-12'::date;
      ?column?
   --------------
    123456-11-12
   (1 row)
   
   The to_char/timestamp not try expect that YYYY = 4-digits (see docs)
   
   test=# select to_char('123456-11-12'::timestamp, 'YYYY DD/Mon');
       to_char
   ---------------
    123456 12/Nov
   (1 row)
    
   I mean that use in to_date() year in the first position is not good 
   idea, better is try:
   
   test=# select to_date('11122000', 'MMDDYYYY');
     to_date
   ------------
    2000-11-12
   
   And 'YY' - it's hell, what is '00'? ... 1900 or 2000 or 20000?
   
   > 
   > >> BTW, on postgres, when I try:
   > >> select to_char( to_date( '20001112', 'YYYYMMDD'), 'YYYYMMDD');
   > >> I get the error:  "ERROR:  Unable to convert timestamp to date"
   > 
   > That seems broken in current sources, too:
   > 
   > regression=# select to_date( '20001112', 'YYYYMMDD');
   > ERROR:  Unable to convert timestamp to date
   
   Yes, because to_date() expect that year is '20001112' and internal
   PG's date/time routines disable convert it. 
   
   test=# select to_date( '2000-1112', 'YYYY-MMDD');
     to_date
   ------------
    2000-11-12
   
   
   > Looks like you've rooted out a number of problems in to_date (which
   > in fairness is new-in-7.0 code).  I've cc'd this to to_date's author,
   > whom I hope will find a fix for 7.1.
   
    How? Create limit for YYYY to 4-digits?
   
   > BTW, direct conversion to timestamp does something sensible in all
   > these cases:
   > 
   > regression=# select  '001112'::timestamp;
   >         ?column?
   > ------------------------
   >  2000-11-12 00:00:00-05
   > (1 row)
   
   Why not 1900?
   
   test=# select to_char('1900-11-12'::date, 'YYDDMM')::timestamp;
           ?column?
   ------------------------
    2000-12-11 00:00:00+01
   (1 row)
   
   What is right here? Really '00' = 2000? .... but input is 1900
   
   > regression=# select  '00001112'::timestamp;
   >    ?column?
   > ---------------
   >  0001-11-12 BC
   > (1 row)
   > 
   > regression=# select  '20001112'::timestamp;
   >         ?column?
   > ------------------------
   >  2000-11-12 00:00:00-05
   > (1 row)
   
   
   Well, thy use this logic for greater year:
   
    test=# select  '20000-11-12'::timestamp;
         ?column?
   ---------------------
    20000-11-12 00:0000
   (1 row)
   
   test=# select  '200001112'::timestamp;
   ERROR:  Bad timestamp external representation '200001112'
   test=#
   
   ???
   
   
    Well, possible solution: to_timestamp/date see if in the input is
   some separator (non-digit char) between YYYY and next template (like DD),
   if separator not here to_date() will expect 4-digits year.
   
    '20001112'	not separator --> 4-digits for year = 2000
    '20000-11-12'	separator '-' --> read it as 20000
   
    '200001112'    not separator --> 4-digits for year = 2000
                                 --> 2-digits for month = 01
                                 --> 2-digits for day = 11
                                 --> last '2' is ignored 
   
   
    With 'YY' *hell* I not sure... add current year IMHO not must be 
   correct. I mean that correct solution is:
   
   test=# select to_date('00-12-11', 'YY-DD-MM');
   ERROR:  Can't convert 'YY' to a real year.
     
   But if users want for their Oracle->PG port use 'YY' as last two digits 
   in the current year, not problem make it....  
    
   				Karel
   
   
   

3. Re: to_date problems (Re: Favor for Postgres User at WSI)

   Kate Collins <klcollins@wsicorp.com> — 2000-11-08T15:16:59Z

   Tom and Karel,
   
   Thank you for your responses.
   
   Based on your email, I have worked out a solution.
   
   The reason I am using the to_date function is because I have two data bases into
   which I am inserting, one is postgres, the other Oracle.  So I need a syntax
   solution which will work with both.
   
   Since I am actually using perl to connect to the data bases, I could modify the code
   to parse the date string before it creates the insert statement.  I have done this
   with other syntax differences, but I am trying to keep this to a minimum.
   
   Another issue is that my input date string is coming from an external source.  It is
   part of a message which I am parsing, and putting in the data base.  The format of
   the date string is YYMMDD.
   
   So, I have created a function:
   
   pbi=# create function my_to_date( text, text) returns timestamp
   pbi-# as 'select $1::timestamp'
   pbi-# language 'sql';
   
   which when run, returns:
   
   pbi=# select my_to_date( '001112', 'yymmdd');
          my_to_date
   ------------------------
    2000-11-12 00:00:00+00
   (1 row)
   
   I made my function look like the the real one, although it does not use the second
   argument.  I may modify this to use the second argument and/or to add punctuation (
   /, :, -) to the time string to make it less ambiguous.
   
   Any way, thank you both again for your help.
   
   Cheers,
   Kate
   
   Karel Zak wrote:
   
   > On Tue, 7 Nov 2000, Tom Lane wrote:
   >
   > > Kate Collins <klcollins@wsicorp.com> writes:
   > > >> In other words it is defaulting to the year 0 (actually year 1 BC, since
   > > >> there is no year 0) instead of 2000.
   > >
   > > Hmm, you're right:
   > >
   > > regression=# select to_date( '001112', 'YYMMDD');
   > >     to_date
   > > ---------------
   > >  0001-11-12 BC
   > > (1 row)
   > >
   > > >> Now I run the equivalent select statement on Oracle, and I get:
   > > >> select to_char( to_date( '001112', 'YYMMDD'), 'YYYYMMDD') from dual;
   > > >> TO_CHAR(TO_DATE('001112','YYMMDD'),'YYYYMMDD')
   > > >> ---------------------------------------------------------------------------
   > > >> 20001112
   > > >>
   > > >> Which is what I expect.
   > > >>
   > > >> Is "YY" suppose to default to the current century or is this an
   > > >> Oracle'ism?
   > >
   > > I dunno whether there is any actual spec for to_date(), but I do agree
   > > that if you've specified a 2-digit YY format, something 2000-centric
   > > would be more useful than the current behavior.
   > >
   > > It doesn't seem to be doing anything particularly sensible with a
   > > 4-digit date, either:
   > >
   > > regression=# select to_date( '00001112', 'YYYYMMDD');
   > >   to_date
   > > ------------
   > >  1112-11-12
   > > (1 row)
   > >
   > > This case I *would* have expected to produce 1 BC, but nope...
   >
   >  Where is *guarantee* that the year is 4-digits?!
   >
   > test=# select '123456-11-12'::date;
   >    ?column?
   > --------------
   >  123456-11-12
   > (1 row)
   >
   > The to_char/timestamp not try expect that YYYY = 4-digits (see docs)
   >
   > test=# select to_char('123456-11-12'::timestamp, 'YYYY DD/Mon');
   >     to_char
   > ---------------
   >  123456 12/Nov
   > (1 row)
   >
   > I mean that use in to_date() year in the first position is not good
   > idea, better is try:
   >
   > test=# select to_date('11122000', 'MMDDYYYY');
   >   to_date
   > ------------
   >  2000-11-12
   >
   > And 'YY' - it's hell, what is '00'? ... 1900 or 2000 or 20000?
   >
   > >
   > > >> BTW, on postgres, when I try:
   > > >> select to_char( to_date( '20001112', 'YYYYMMDD'), 'YYYYMMDD');
   > > >> I get the error:  "ERROR:  Unable to convert timestamp to date"
   > >
   > > That seems broken in current sources, too:
   > >
   > > regression=# select to_date( '20001112', 'YYYYMMDD');
   > > ERROR:  Unable to convert timestamp to date
   >
   > Yes, because to_date() expect that year is '20001112' and internal
   > PG's date/time routines disable convert it.
   >
   > test=# select to_date( '2000-1112', 'YYYY-MMDD');
   >   to_date
   > ------------
   >  2000-11-12
   >
   > > Looks like you've rooted out a number of problems in to_date (which
   > > in fairness is new-in-7.0 code).  I've cc'd this to to_date's author,
   > > whom I hope will find a fix for 7.1.
   >
   >  How? Create limit for YYYY to 4-digits?
   >
   > > BTW, direct conversion to timestamp does something sensible in all
   > > these cases:
   > >
   > > regression=# select  '001112'::timestamp;
   > >         ?column?
   > > ------------------------
   > >  2000-11-12 00:00:00-05
   > > (1 row)
   >
   > Why not 1900?
   >
   > test=# select to_char('1900-11-12'::date, 'YYDDMM')::timestamp;
   >         ?column?
   > ------------------------
   >  2000-12-11 00:00:00+01
   > (1 row)
   >
   > What is right here? Really '00' = 2000? .... but input is 1900
   >
   > > regression=# select  '00001112'::timestamp;
   > >    ?column?
   > > ---------------
   > >  0001-11-12 BC
   > > (1 row)
   > >
   > > regression=# select  '20001112'::timestamp;
   > >         ?column?
   > > ------------------------
   > >  2000-11-12 00:00:00-05
   > > (1 row)
   >
   > Well, thy use this logic for greater year:
   >
   >  test=# select  '20000-11-12'::timestamp;
   >       ?column?
   > ---------------------
   >  20000-11-12 00:0000
   > (1 row)
   >
   > test=# select  '200001112'::timestamp;
   > ERROR:  Bad timestamp external representation '200001112'
   > test=#
   >
   > ???
   >
   >  Well, possible solution: to_timestamp/date see if in the input is
   > some separator (non-digit char) between YYYY and next template (like DD),
   > if separator not here to_date() will expect 4-digits year.
   >
   >  '20001112'     not separator --> 4-digits for year = 2000
   >  '20000-11-12'  separator '-' --> read it as 20000
   >
   >  '200001112'    not separator --> 4-digits for year = 2000
   >                               --> 2-digits for month = 01
   >                               --> 2-digits for day = 11
   >                               --> last '2' is ignored
   >
   >  With 'YY' *hell* I not sure... add current year IMHO not must be
   > correct. I mean that correct solution is:
   >
   > test=# select to_date('00-12-11', 'YY-DD-MM');
   > ERROR:  Can't convert 'YY' to a real year.
   >
   > But if users want for their Oracle->PG port use 'YY' as last two digits
   > in the current year, not problem make it....
   >
   >                                 Karel
   
   --
   =================================================
   Katherine (Kate) L. Collins
   Senior Software Engineer/Meteorologist
   Weather Services International (WSI Corporation)
   4 Federal Street
   Billerica, MA 01821
   EMAIL:  klcollins@wsicorp.com
   PHONE:  (978) 670-5110
   FAX:    (978) 670-5100
   http://www.intellicast.com
   
   
   
   

4. Re: Re: to_date problems (Re: Favor for Postgres User at WSI)

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-08T15:27:17Z

   Karel Zak <zakkr@zf.jcu.cz> writes:
   >> I dunno whether there is any actual spec for to_date(), but I do agree
   >> that if you've specified a 2-digit YY format, something 2000-centric
   >> would be more useful than the current behavior.
   >> 
   >> It doesn't seem to be doing anything particularly sensible with a
   >> 4-digit date, either:
   >> 
   >> regression=# select to_date( '00001112', 'YYYYMMDD');
   >> to_date
   >> ------------
   >> 1112-11-12
   >> (1 row)
   >> 
   >> This case I *would* have expected to produce 1 BC, but nope...
   
   >  Where is *guarantee* that the year is 4-digits?!
   
   Who said anything about a guarantee?  In the cases at hand, the number
   of Y's appearing in the format string should give you a sufficient clue
   about how to behave.  In fact, if I were you I'd reject a format string
   that had a number of Y's other than 2 or 4, because then it really isn't
   very clear what you're supposed to do.
   
   > And 'YY' - it's hell, what is '00'? ... 1900 or 2000 or 20000?
   
   It should work the same as the timestamp input converter.  Thomas
   Lockhart could give you more details about exactly what that code does.
   It's probably assuming that the intended value of a 2-digit year is
   between 1970 and 2069, or some other 100-year range that contains
   current time.
   
   Assuming that YY = '00' means 1 BC is definitely not reasonable
   behavior, at least not in the USA.  2-digit year notations are
   very common here, and no one uses them that way ;-)
   
   			regards, tom lane
   
   

5. Re: Re: to_date problems (Re: Favor for Postgres User at WSI)

   Karel Zak <zakkr@zf.jcu.cz> — 2000-11-09T07:41:30Z

   On Wed, 8 Nov 2000, Tom Lane wrote:
   
   > Karel Zak <zakkr@zf.jcu.cz> writes:
   > >> I dunno whether there is any actual spec for to_date(), but I do agree
   > >> that if you've specified a 2-digit YY format, something 2000-centric
   > >> would be more useful than the current behavior.
   > >> 
   > >> It doesn't seem to be doing anything particularly sensible with a
   > >> 4-digit date, either:
   > >> 
   > >> regression=# select to_date( '00001112', 'YYYYMMDD');
   > >> to_date
   > >> ------------
   > >> 1112-11-12
   > >> (1 row)
   > >> 
   > >> This case I *would* have expected to produce 1 BC, but nope...
   > 
   > >  Where is *guarantee* that the year is 4-digits?!
   > 
   > Who said anything about a guarantee?  In the cases at hand, the number
   > of Y's appearing in the format string should give you a sufficient clue
   > about how to behave.  In fact, if I were you I'd reject a format string
   > that had a number of Y's other than 2 or 4, because then it really isn't
   > very clear what you're supposed to do.
   > 
   > > And 'YY' - it's hell, what is '00'? ... 1900 or 2000 or 20000?
   > 
   > It should work the same as the timestamp input converter.  Thomas
   > Lockhart could give you more details about exactly what that code does.
   > It's probably assuming that the intended value of a 2-digit year is
   > between 1970 and 2069, or some other 100-year range that contains
   > current time.
   > 
   > Assuming that YY = '00' means 1 BC is definitely not reasonable
   > behavior, at least not in the USA.  2-digit year notations are
   > very common here, and no one uses them that way ;-)
   
    OK, OK... we gain me. Support something for this small country between 
   Canada and Mexico is probably pretty think. But else in the heaven I will
   say that 'YY' is the hell...
   
    7.1 resolve it.
   						Karel
   
   
   
   
   

6. Re: Re: to_date problems (Re: Favor for Postgres User at WSI)

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-11-11T17:50:13Z

   (Sorry for diving in late; I was out of town the last few days)
   
   > > This case I *would* have expected to produce 1 BC, but nope...
   >  Where is *guarantee* that the year is 4-digits?!
   
   There is no guarantee of only four digits, but there is a convention
   that two digit years refer to the current/previous/next century. I've
   worked through the same issues with the other date/time types, and have
   evolved the code through exactly the same path you are taking. And been
   annoyed by all of the arcane details in doing it ;)
   
   > test=# select '123456-11-12'::date;
   >    ?column?
   > --------------
   >  123456-11-12
   > (1 row)
   > The to_char/timestamp not try expect that YYYY = 4-digits (see docs)
   
   No problem there. But it will be good to conform to the 4 digit/2 digit
   conventions when 4 or 2 digits appear in the year field.
   
   > And 'YY' - it's hell, what is '00'? ... 1900 or 2000 or 20000?
   
   Depends on what year is specified. Our Y2K statement (may it rest in
   peace; seems pretty silly now, eh?) specifies the expected behavior. We
   currently use 1970 as the transition for centuries with two digit years
   (I did this as a nod to Unix) but I believe it is more common to use
   1950 as the transition year. I don't feel a need to move to this more
   common convention, but would be willing to do so if others see that as
   helpful or important.
   
   > > That seems broken in current sources, too:
   > > regression=# select to_date( '20001112', 'YYYYMMDD');
   > > ERROR:  Unable to convert timestamp to date
   > Yes, because to_date() expect that year is '20001112' and internal
   > PG's date/time routines disable convert it.
   
   The other date/time routines have heuristics when parsing long integer
   strings.
   
   2 chars is a yy, mm, or dd
   3 chars is a day number
   4 chars is a yyyy
   5 chars is a yyddd
   6 chars is a yymmdd
   
   These are documented in the appendix on parsing date/times.
   
   > test=# select to_date( '2000-1112', 'YYYY-MMDD');
   >   to_date
   > ------------
   >  2000-11-12
   > > Looks like you've rooted out a number of problems in to_date (which
   > > in fairness is new-in-7.0 code).  I've cc'd this to to_date's author,
   > > whom I hope will find a fix for 7.1.
   >  How? Create limit for YYYY to 4-digits?
   
   Solved with heuristics.
   
   > > BTW, direct conversion to timestamp does something sensible in all
   > > these cases:
   > > regression=# select  '001112'::timestamp;
   > >         ?column?
   > > ------------------------
   > >  2000-11-12 00:00:00-05
   > > (1 row)
   > Why not 1900?
   
   Because of the common and documented cutoff date (1970 currently, 1950
   in some other apps) used to solve this problem.
   
   > test=# select to_char('1900-11-12'::date, 'YYDDMM')::timestamp;
   >         ?column?
   > ------------------------
   >  2000-12-11 00:00:00+01
   > (1 row)
   > What is right here? Really '00' = 2000? .... but input is 1900
   
   That is operator error; converting a year outside the cutoff range to a
   string and then converting it back to a date/time type gets you what you
   deserve for using two-digit years. (I know you did this for
   illustration, but two digit years can be dangerous, as you are pointing
   out).
   
   > test=# select  '200001112'::timestamp;
   > ERROR:  Bad timestamp external representation '200001112'
   > ???
   
   I was going to claim that the "long year" is covered in the existing
   heuristics, but I'll now claim that rejecting arbitrarily long,
   undelimited ISO dates is preferred ;)
   
   >  Well, possible solution: to_timestamp/date see if in the input is
   > some separator (non-digit char) between YYYY and next template (like DD),
   > if separator not here to_date() will expect 4-digits year.
   >  '20001112'     not separator --> 4-digits for year = 2000
   >  '20000-11-12'  separator '-' --> read it as 20000
   >  '200001112'    not separator --> 4-digits for year = 2000
   >                               --> 2-digits for month = 01
   >                               --> 2-digits for day = 11
   >                               --> last '2' is ignored
   >  With 'YY' *hell* I not sure... add current year IMHO not must be
   > correct. I mean that correct solution is:
   > test=# select to_date('00-12-11', 'YY-DD-MM');
   > ERROR:  Can't convert 'YY' to a real year.
   > But if users want for their Oracle->PG port use 'YY' as last two digits
   > in the current year, not problem make it....
   
   Karel, I can help polish the heuristics with you. That 1970/1950
   convention is something you can rely on.
   
                           - Thomas
   
   

7. Re: Re: to_date problems (Re: Favor for Postgres User at WSI)

   Karel Zak <zakkr@zf.jcu.cz> — 2000-11-12T11:18:14Z

   On Sat, 11 Nov 2000, Thomas Lockhart wrote:
   
   > > > This case I *would* have expected to produce 1 BC, but nope...
   > >  Where is *guarantee* that the year is 4-digits?!
   > 
   > There is no guarantee of only four digits, but there is a convention
   > that two digit years refer to the current/previous/next century. I've
   > worked through the same issues with the other date/time types, and have
   > evolved the code through exactly the same path you are taking. And been
   > annoyed by all of the arcane details in doing it ;)
   > 
   > > test=# select '123456-11-12'::date;
   > >    ?column?
   > > --------------
   > >  123456-11-12
   > > (1 row)
   > > The to_char/timestamp not try expect that YYYY = 4-digits (see docs)
   > 
   > No problem there. But it will be good to conform to the 4 digit/2 digit
   > conventions when 4 or 2 digits appear in the year field.
   > 
   > > And 'YY' - it's hell, what is '00'? ... 1900 or 2000 or 20000?
   > 
   > Depends on what year is specified. Our Y2K statement (may it rest in
   > peace; seems pretty silly now, eh?) specifies the expected behavior. We
   > currently use 1970 as the transition for centuries with two digit years
   > (I did this as a nod to Unix) but I believe it is more common to use
   > 1950 as the transition year. I don't feel a need to move to this more
   > common convention, but would be willing to do so if others see that as
   > helpful or important.
   > 
   > > > That seems broken in current sources, too:
   > > > regression=# select to_date( '20001112', 'YYYYMMDD');
   > > > ERROR:  Unable to convert timestamp to date
   > > Yes, because to_date() expect that year is '20001112' and internal
   > > PG's date/time routines disable convert it.
   > 
   > The other date/time routines have heuristics when parsing long integer
   > strings.
   > 
   > 2 chars is a yy, mm, or dd
   > 3 chars is a day number
   > 4 chars is a yyyy
   > 5 chars is a yyddd
   > 6 chars is a yymmdd
   
   Yes, but in to_char/date must be parsing regulate by format-template and
   inputs can be very heterogeneous. 
   
   > These are documented in the appendix on parsing date/times.
   
   OK.
   
   > 
   > > test=# select to_date( '2000-1112', 'YYYY-MMDD');
   > >   to_date
   > > ------------
   > >  2000-11-12
   > > > Looks like you've rooted out a number of problems in to_date (which
   > > > in fairness is new-in-7.0 code).  I've cc'd this to to_date's author,
   > > > whom I hope will find a fix for 7.1.
   > >  How? Create limit for YYYY to 4-digits?
   > 
   > Solved with heuristics.
   > 
   > > > BTW, direct conversion to timestamp does something sensible in all
   > > > these cases:
   > > > regression=# select  '001112'::timestamp;
   > > >         ?column?
   > > > ------------------------
   > > >  2000-11-12 00:00:00-05
   > > > (1 row)
   > > Why not 1900?
   > 
   > Because of the common and documented cutoff date (1970 currently, 1950
   > in some other apps) used to solve this problem.
   > 
   > > test=# select to_char('1900-11-12'::date, 'YYDDMM')::timestamp;
   > >         ?column?
   > > ------------------------
   > >  2000-12-11 00:00:00+01
   > > (1 row)
   > > What is right here? Really '00' = 2000? .... but input is 1900
   > 
   > That is operator error; converting a year outside the cutoff range to a
   > string and then converting it back to a date/time type gets you what you
   > deserve for using two-digit years. (I know you did this for
   > illustration, but two digit years can be dangerous, as you are pointing
   > out).
   > 
   > > test=# select  '200001112'::timestamp;
   > > ERROR:  Bad timestamp external representation '200001112'
   > > ???
   > 
   > I was going to claim that the "long year" is covered in the existing
   > heuristics, but I'll now claim that rejecting arbitrarily long,
   > undelimited ISO dates is preferred ;)
   > 
   > >  Well, possible solution: to_timestamp/date see if in the input is
   > > some separator (non-digit char) between YYYY and next template (like DD),
   > > if separator not here to_date() will expect 4-digits year.
   > >  '20001112'     not separator --> 4-digits for year = 2000
   > >  '20000-11-12'  separator '-' --> read it as 20000
   > >  '200001112'    not separator --> 4-digits for year = 2000
   > >                               --> 2-digits for month = 01
   > >                               --> 2-digits for day = 11
   > >                               --> last '2' is ignored
   > >  With 'YY' *hell* I not sure... add current year IMHO not must be
   > > correct. I mean that correct solution is:
   > > test=# select to_date('00-12-11', 'YY-DD-MM');
   > > ERROR:  Can't convert 'YY' to a real year.
   > > But if users want for their Oracle->PG port use 'YY' as last two digits
   > > in the current year, not problem make it....
   > 
   > Karel, I can help polish the heuristics with you. That 1970/1950
   > convention is something you can rely on.
   
    I try "steal" and study your code from date/time routines and try 
   implement correct (mean like others PG routines) YY/YYY conversion
   with 1970/1950. For 4-digits or "long years" I use separator analyze.
   
    I don't worry, we have to_char/date already better than original 
   Oracle's to_char() :-)
   
   
   
   
   
   

8. Re: Re: to_date problems (Re: Favor for Postgres User at WSI)

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-11-12T16:10:14Z

   >  I don't worry, we have to_char/date already better than original
   > Oracle's to_char() :-)
   
   :)
   
   Yes, and you'll find that the code will settle down and need very little
   attention from here on. Our other date/time code has been around for 3
   or 4 years now, and goes months without anyone even asking about edge
   cases.
   
                          - Thomas
   
   

9. Re: Re: to_date problems (Re: Favor for Postgres User at WSI)

   Andrew McMillan <andrew@catalyst.net.nz> — 2000-11-12T18:54:59Z

   Thomas Lockhart wrote:
   > 
   > Because of the common and documented cutoff date (1970 currently, 1950
   > in some other apps) used to solve this problem.
   
   Most database software I have seen uses some form of setting to control
   the actual date used here, and that is the most long-term solution.
   
   something like:
   set CENTURY_WINDOW TO '1980';
   
   Would be nicest.
   
   Regards,
   					Andrew.
   -- 
   _____________________________________________________________________
               Andrew McMillan, e-mail: Andrew@cat-it.co.nz
   Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
   Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267