Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

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?