Conference orarep::nomahs::sql

3890.0. "Extract, seconds & decimal places" by ukvms3.uk.oracle.com::LWILES (Louise Wiles, UK Rdb support) Wed May 28 1997 12:59

    Hi,

    Rdb V7.0-01
    Alpha VMS V6.2

    I have a problem with extract, seconds & scale. From the SQL Ref
    Manual, it says that

        'the datatype returned is a signed longword of scale 0, unless
        the date-time field is SECOND. If the SECOND field is selected,
        then the scale is set to 2.'

    I have a query which gives the number of seconds between a given date &
    current_timestamp, but when this is incorporated into a procedure which
    then calculates an interval, it truncates the two decimal places.

    Can someone explain why this is?

    set ansi date on;
    select
    extract
        (second from 
            ((current_timestamp 
              - 
              cast('1970-01-01:00:00:00.00' as timestamp(2)) second))
    from rdb$database;

        5848364.36
    1 row selected

    Same thing done in two steps:

    declare :int_sec interval second;
    Declare :sec_int bigint;
    Begin
        set :int_sec =
        (current_timestamp 
         - 
         cast('1970-01-01:00:00:00.00' as timestamp(2)) ) second;
        Set :sec_int = Extract(second From :int_sec);
    End;
    print :sec_int;
             SEC_INT
             5848371

    Thanks,
    Louise.

So what is the problem?  This looks fine to me.

Ian

    I think you want
        Declare :sec_int bigint(2);
    

    �I think you want
    �    Declare :sec_int bigint(2);
    
    I do, yes. Thanks.