Re: roundoff problem in time datatype

On 10/13/05, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Tom,
>
> > I think my preference is to allow '24:00:00' (but not anything larger)
> > as a valid input value of the time datatypes. This for two reasons:
> > * existing dump files may contain such values
> > * it's consistent with allowing, eg, '12:13:60', which we
> > allow even though it's certainly not a valid leap second.

we shouldn't be allowing such timestamps! We should enforce only the
canonical formats of any datatype. Imagine what chaos would have been
caused if we didn't have IEEE specifications for the floating point
numbers!!!

>
> It's also consistent with how several other RDBMSes do things (SQL Server,
> MySQL), and several programming languages.

Just wanted to note that this is not really consistent with other
databases. For eg. SQL Server's o/p is shown below.

select convert( datetime, '23:59:59.998' )
1900-01-01 23:59:59.997

select convert( datetime, '23:59:59.999' )
1900-01-02 00:00:00.000 /* the date changes but the time remains
under 24:00:00 */

select convert( datetime, '24:00:00' )
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

Moreover, 24:00:00 not in canonical format so it should not be encoraged at all.

Gujreet.