Обсуждение: to_date problems (Re: Favor for Postgres User at WSI)

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

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

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

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

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

(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

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() :-)

>  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

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