sybperl-l Archive

From: "David LANDGREN" <dlandgre at bpinet dot com>
Subject: Differences in convert()ed AM/PM times
Date: Jan 22 2002 4:02PM

Suppose I have a table with a date column. I might be interested in finding
the most recent date. If I write something like this

    my $date = do {
        my $r = $db->selectall_arrayref(
            'select max(my_date) from t'
        );
        defined $r and defined $r->[0] ? $r->[0][0] : undef;
    };
    print $date;

It prints out instead "Jan 15 2002 7:08PM".

If instead, I use the convert function to specify how the date value should
 be formatted:

    my $date = do {
        my $r = $db->selectall_arrayref(
            'select convert(char(20),max(my_date, 109) from t'
        );
        defined $r and defined $r->[0] ? $r->[0][0] : undef;
    };
    print $date;

It prints out "Jan 15 2002 7:08:19".

The default style gives me the AM/PM sign, and the 109 style gives me the
seconds, but not the AM/PM indicator (in direct contradiction to what the
documentation says "mon dd yyyy hh:mm:sss AM (or PM)"). This is Sybase
11.5.1 on Solaris.

The trouble is, I am inserting records into a table, but then I can no
longer pull them out with a similar select max(date) where clause, because
depending on what of the above I insert, it will be subsequently
interpreted as either 7:08:19AM or 7:08:00PM, neither of which are equal to
7:08:19PM, which is indeed the most recent date in the table.

Unfortunately, this is not a bug in DBD::Sybase, for the same behaviour
crops up when using SQL Advantage.

Is this some kind of locale issue? I guess I can kluge something up to
fetch the date and the time in separate fields and then bolt them together
but that strikes me as being distinctly sub-optimal. Can I coax a 24 hour
time value out of Sybase in some way?

Thanks,
David