PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
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.

Regards,

Abe Crabtree


-----Original Message-----
From: laksjan [mailto:laksjan@hotmail.com]
Sent: 23 January 2002 02:23
To: SYBPERL-L
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.

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