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: "TK dot Lakshminarasimhan" <laksjan at hotmail dot com>
Subject: Re: Differences in convert()ed AM/PM times
Date: Jan 23 2002 2:23AM

Hi David,
Sybase behaves some funny. The simplest and best bet is to use the substring
from 12th position of the normal datetime "Jan 15 2002 7:08PM" till the end
of the string.
you can use the following sql to get that.
    substring(DATE_TIME_COL, 12)    => 7:08PM.
In the above SQL, we say that get the substring() starting from position 12
till the end of the string.

If I can think of any other way, I will let you know.


----- Original Message -----
From: "David LANDGREN" 
To: "SybPerl Discussion List" 
Sent: Tuesday, January 22, 2002 11:02 AM
Subject: Differences in convert()ed AM/PM times

> Suppose I have a table with a date column. I might be interested in
> 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
>  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
> 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