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: abe dot crabtree at citicorp dot com
Subject: Re: Differences in convert()ed AM/PM times
Date: Jan 23 2002 9:02AM

I think the fundamental problem with:

	select convert(char(20),max(my_date, 109) from t

is that the string is too short and so the time part of the string is truncated.

I tried the following (no perl involved - I don't think this is a perl problem) 

1> select convert(char(20),getdate(),109)
2> go

 Jan 23 2002  8:54:52

(1 row affected)

1> select convert(char(28),getdate(),109)
2> go

 Jan 23 2002  8:55:04:713AM

(1 row affected)

I'm sure the substring technique is good too.


Abe Crabtree

-----Original Message-----
From: laksjan []
Sent: 23 January 2002 02:23
Cc: laksjan
Subject: Re: Differences in convert()ed AM/PM times

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