|
|
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.
TK.
----- 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
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
>
>
|
