Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

smalldatetime issue

10 posts in General Discussion (old) Last posting was on 2009-04-22 11:09:48.0Z
Charles Huang Posted on 2009-03-05 02:36:31.0Z
From: "Charles Huang" <charles.huang@sybase.com>
Newsgroups: sybase.public.sqlanywhere
Subject: smalldatetime issue
Lines: 16
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1933
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1933
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49af3aaf@forums-1-dub>
Date: 4 Mar 2009 18:36:31 -0800
X-Trace: forums-1-dub 1236220591 10.22.241.152 (4 Mar 2009 18:36:31 -0800)
X-Original-Trace: 4 Mar 2009 18:36:31 -0800, vip152.sybase.com
X-Authenticated-User: usecase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:259
Article PK: 866700

Hi

I found that when I insert to a smalldatetime typed column, second value
loses.

but according to ASA documentation, it's actually timestamp.

just curious, is it a intended design or it's a bug?



Regards

Charles


Breck Carter [TeamSybase] Posted on 2009-03-05 11:20:13.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: smalldatetime issue
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com>
References: <49af3aaf@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 5 Mar 2009 03:20:13 -0800
X-Trace: forums-1-dub 1236252013 10.22.241.152 (5 Mar 2009 03:20:13 -0800)
X-Original-Trace: 5 Mar 2009 03:20:13 -0800, vip152.sybase.com
Lines: 84
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:260
Article PK: 866701

Not a bug, especially since it does NOT drop the seconds value; you
are probably seeing a client-side or application program or interface
issue.

Here is the story: There is no such thing as the "smalldatetime" data
type in SQL Anywhere, in spite of the fact the Help calls it a "data
type". There is something called the "smalldatetime" *domain*, and it
is there only for backward compatibility with applications that use
Transact SQL.

If you are not seeing seconds, then one of two things is happening:
Your code is truncating the value before storing it, or your code is
truncating the value when retrieving it. By "your code" I mean
anything that is not inside the database engine; it could be the
interface software, the development tools, the class library you're
using, whatever. Something is pretending that smalldatetime is really
small :)

Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that by
default timestamps are returned with seconds to three decimal places,
but you can change that to six, and it ALWAYS stores what you give it
(up to six decimal places).

CREATE TABLE t (
pkey INTEGER NOT NULL PRIMARY KEY,
now SMALLDATETIME NOT NULL );

INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
COMMIT;

SELECT * FROM t;

pkey,now
1,'2009-03-05 06:04:58.123'

SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';

SELECT * FROM t;

pkey,now
1,'2009-03-05 06:04:58.123456'

=====

FWIW the confusion surrounding domains (I hate them) is exactly the
reason Foxhound shows the true data types:

-- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print -
Foxhound © 2007 RisingRoad

CREATE TABLE DBA.t ( -- empty
pkey /* PK */ INTEGER NOT NULL,
now TIMESTAMP NOT NULL, -- domain smalldatetime
CONSTRAINT ASA77 PRIMARY KEY (
pkey )
);

Breck


On 4 Mar 2009 18:36:31 -0800, "Charles Huang"

<charles.huang@sybase.com> wrote:

>Hi
>
>I found that when I insert to a smalldatetime typed column, second value
>loses.
>
>but according to ASA documentation, it's actually timestamp.
>
>just curious, is it a intended design or it's a bug?
>
>
>
>Regards
>
>Charles
>

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Charles Huang Posted on 2009-03-18 02:27:23.0Z
From: "Charles Huang" <charles.huang@sybase.com>
Newsgroups: sybase.public.sqlanywhere
References: <49af3aaf@forums-1-dub> <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com>
Subject: Re: smalldatetime issue
Lines: 5
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1933
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1933
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49c05c0b$1@forums-1-dub>
Date: 17 Mar 2009 18:27:23 -0800
X-Trace: forums-1-dub 1237343243 10.22.241.152 (17 Mar 2009 18:27:23 -0800)
X-Original-Trace: 17 Mar 2009 18:27:23 -0800, vip152.sybase.com
X-Authenticated-User: usecase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:264
Article PK: 866703

thanks, very good information

Charles


Charles Huang Posted on 2009-03-18 03:11:32.0Z
From: "Charles Huang" <charles.huang@sybase.com>
Newsgroups: sybase.public.sqlanywhere
References: <49af3aaf@forums-1-dub> <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com>
Subject: Re: smalldatetime issue
Lines: 92
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1933
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1933
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49c06664$1@forums-1-dub>
Date: 17 Mar 2009 19:11:32 -0800
X-Trace: forums-1-dub 1237345892 10.22.241.152 (17 Mar 2009 19:11:32 -0800)
X-Original-Trace: 17 Mar 2009 19:11:32 -0800, vip152.sybase.com
X-Authenticated-User: usecase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:265
Article PK: 866706

maybe it's jconn3 issue, we are using jdbc to get data from asa.

charles

"Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com> wrote in
message news:njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com...
> Not a bug, especially since it does NOT drop the seconds value; you
> are probably seeing a client-side or application program or interface
> issue.
>
> Here is the story: There is no such thing as the "smalldatetime" data
> type in SQL Anywhere, in spite of the fact the Help calls it a "data
> type". There is something called the "smalldatetime" *domain*, and it
> is there only for backward compatibility with applications that use
> Transact SQL.
>
> If you are not seeing seconds, then one of two things is happening:
> Your code is truncating the value before storing it, or your code is
> truncating the value when retrieving it. By "your code" I mean
> anything that is not inside the database engine; it could be the
> interface software, the development tools, the class library you're
> using, whatever. Something is pretending that smalldatetime is really
> small :)
>
> Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that by
> default timestamps are returned with seconds to three decimal places,
> but you can change that to six, and it ALWAYS stores what you give it
> (up to six decimal places).
>
> CREATE TABLE t (
> pkey INTEGER NOT NULL PRIMARY KEY,
> now SMALLDATETIME NOT NULL );
>
> INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
> COMMIT;
>
> SELECT * FROM t;
>
> pkey,now
> 1,'2009-03-05 06:04:58.123'
>
> SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';
>
> SELECT * FROM t;
>
> pkey,now
> 1,'2009-03-05 06:04:58.123456'
>
> =====
>
> FWIW the confusion surrounding domains (I hate them) is exactly the
> reason Foxhound shows the true data types:
>
> -- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print -
> Foxhound © 2007 RisingRoad
>
> CREATE TABLE DBA.t ( -- empty
> pkey /* PK */ INTEGER NOT NULL,
> now TIMESTAMP NOT NULL, -- domain smalldatetime
> CONSTRAINT ASA77 PRIMARY KEY (
> pkey )
> );
>
> Breck
>
>
> On 4 Mar 2009 18:36:31 -0800, "Charles Huang"
> <charles.huang@sybase.com> wrote:
>
> >Hi
> >
> >I found that when I insert to a smalldatetime typed column, second value
> >loses.
> >
> >but according to ASA documentation, it's actually timestamp.
> >
> >just curious, is it a intended design or it's a bug?
> >
> >
> >
> >Regards
> >
> >Charles
> >
>
> --
> Breck Carter http://sqlanywhere.blogspot.com/
>
> RisingRoad SQL Anywhere and MobiLink Professional Services
> breck.carter@risingroad.com


Breck Carter [TeamSybase] Posted on 2009-03-18 06:01:43.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: smalldatetime issue
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <k731s4530j3go70o0ok2p681j8r95n7up8@4ax.com>
References: <49af3aaf@forums-1-dub> <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com> <49c06664$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 17 Mar 2009 22:01:43 -0800
X-Trace: forums-1-dub 1237356103 10.22.241.152 (17 Mar 2009 22:01:43 -0800)
X-Original-Trace: 17 Mar 2009 22:01:43 -0800, vip152.sybase.com
Lines: 113
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:266
Article PK: 866704

What version of SQL Anywere are you using? What JDBC driver are you
using?

Glenn Paulley has begun a discussion of the differences between
jConnect and the (better, IMO) iAnywhere JDBC driver here:
http://iablog.sybase.com/paulley/2009/03/differences-between-jconnect-and-the-ianywhere-jdbc-driver-part-un/

I have exactly one client who prefers jConnect, the rest prefer the
iAnywhere driver. And I'm not sure that one client still hold the same
opinion.

Breck

On 17 Mar 2009 19:11:32 -0800, "Charles Huang"

<charles.huang@sybase.com> wrote:

>maybe it's jconn3 issue, we are using jdbc to get data from asa.
>
>charles
>
>"Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com> wrote in
>message news:njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com...
>> Not a bug, especially since it does NOT drop the seconds value; you
>> are probably seeing a client-side or application program or interface
>> issue.
>>
>> Here is the story: There is no such thing as the "smalldatetime" data
>> type in SQL Anywhere, in spite of the fact the Help calls it a "data
>> type". There is something called the "smalldatetime" *domain*, and it
>> is there only for backward compatibility with applications that use
>> Transact SQL.
>>
>> If you are not seeing seconds, then one of two things is happening:
>> Your code is truncating the value before storing it, or your code is
>> truncating the value when retrieving it. By "your code" I mean
>> anything that is not inside the database engine; it could be the
>> interface software, the development tools, the class library you're
>> using, whatever. Something is pretending that smalldatetime is really
>> small :)
>>
>> Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that by
>> default timestamps are returned with seconds to three decimal places,
>> but you can change that to six, and it ALWAYS stores what you give it
>> (up to six decimal places).
>>
>> CREATE TABLE t (
>> pkey INTEGER NOT NULL PRIMARY KEY,
>> now SMALLDATETIME NOT NULL );
>>
>> INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
>> COMMIT;
>>
>> SELECT * FROM t;
>>
>> pkey,now
>> 1,'2009-03-05 06:04:58.123'
>>
>> SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';
>>
>> SELECT * FROM t;
>>
>> pkey,now
>> 1,'2009-03-05 06:04:58.123456'
>>
>> =====
>>
>> FWIW the confusion surrounding domains (I hate them) is exactly the
>> reason Foxhound shows the true data types:
>>
>> -- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print -
>> Foxhound © 2007 RisingRoad
>>
>> CREATE TABLE DBA.t ( -- empty
>> pkey /* PK */ INTEGER NOT NULL,
>> now TIMESTAMP NOT NULL, -- domain smalldatetime
>> CONSTRAINT ASA77 PRIMARY KEY (
>> pkey )
>> );
>>
>> Breck
>>
>>
>> On 4 Mar 2009 18:36:31 -0800, "Charles Huang"
>> <charles.huang@sybase.com> wrote:
>>
>> >Hi
>> >
>> >I found that when I insert to a smalldatetime typed column, second value
>> >loses.
>> >
>> >but according to ASA documentation, it's actually timestamp.
>> >
>> >just curious, is it a intended design or it's a bug?
>> >
>> >
>> >
>> >Regards
>> >
>> >Charles
>> >
>>
>> --
>> Breck Carter http://sqlanywhere.blogspot.com/
>>
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> breck.carter@risingroad.com
>

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Breck Carter [TeamSybase] Posted on 2009-03-18 06:04:01.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: smalldatetime issue
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <1l31s4phh4vjlnd3o9p87ds466vim2soas@4ax.com>
References: <49af3aaf@forums-1-dub> <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com> <49c06664$1@forums-1-dub> <k731s4530j3go70o0ok2p681j8r95n7up8@4ax.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 17 Mar 2009 22:04:01 -0800
X-Trace: forums-1-dub 1237356241 10.22.241.152 (17 Mar 2009 22:04:01 -0800)
X-Original-Trace: 17 Mar 2009 22:04:01 -0800, vip152.sybase.com
Lines: 122
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:267
Article PK: 866707

Wait, you did say "jconn3"... not enough coffee yet :)

What version of SQL Anywhere?

Breck

On 17 Mar 2009 22:01:43 -0800, "Breck Carter [TeamSybase]"

<NOSPAM__breck.carter@gmail.com> wrote:

>What version of SQL Anywere are you using? What JDBC driver are you
>using?
>
>Glenn Paulley has begun a discussion of the differences between
>jConnect and the (better, IMO) iAnywhere JDBC driver here:
>http://iablog.sybase.com/paulley/2009/03/differences-between-jconnect-and-the-ianywhere-jdbc-driver-part-un/
>
>I have exactly one client who prefers jConnect, the rest prefer the
>iAnywhere driver. And I'm not sure that one client still hold the same
>opinion.
>
>Breck
>
>On 17 Mar 2009 19:11:32 -0800, "Charles Huang"
><charles.huang@sybase.com> wrote:
>
>>maybe it's jconn3 issue, we are using jdbc to get data from asa.
>>
>>charles
>>
>>"Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com> wrote in
>>message news:njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com...
>>> Not a bug, especially since it does NOT drop the seconds value; you
>>> are probably seeing a client-side or application program or interface
>>> issue.
>>>
>>> Here is the story: There is no such thing as the "smalldatetime" data
>>> type in SQL Anywhere, in spite of the fact the Help calls it a "data
>>> type". There is something called the "smalldatetime" *domain*, and it
>>> is there only for backward compatibility with applications that use
>>> Transact SQL.
>>>
>>> If you are not seeing seconds, then one of two things is happening:
>>> Your code is truncating the value before storing it, or your code is
>>> truncating the value when retrieving it. By "your code" I mean
>>> anything that is not inside the database engine; it could be the
>>> interface software, the development tools, the class library you're
>>> using, whatever. Something is pretending that smalldatetime is really
>>> small :)
>>>
>>> Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that by
>>> default timestamps are returned with seconds to three decimal places,
>>> but you can change that to six, and it ALWAYS stores what you give it
>>> (up to six decimal places).
>>>
>>> CREATE TABLE t (
>>> pkey INTEGER NOT NULL PRIMARY KEY,
>>> now SMALLDATETIME NOT NULL );
>>>
>>> INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
>>> COMMIT;
>>>
>>> SELECT * FROM t;
>>>
>>> pkey,now
>>> 1,'2009-03-05 06:04:58.123'
>>>
>>> SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';
>>>
>>> SELECT * FROM t;
>>>
>>> pkey,now
>>> 1,'2009-03-05 06:04:58.123456'
>>>
>>> =====
>>>
>>> FWIW the confusion surrounding domains (I hate them) is exactly the
>>> reason Foxhound shows the true data types:
>>>
>>> -- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print -
>>> Foxhound © 2007 RisingRoad
>>>
>>> CREATE TABLE DBA.t ( -- empty
>>> pkey /* PK */ INTEGER NOT NULL,
>>> now TIMESTAMP NOT NULL, -- domain smalldatetime
>>> CONSTRAINT ASA77 PRIMARY KEY (
>>> pkey )
>>> );
>>>
>>> Breck
>>>
>>>
>>> On 4 Mar 2009 18:36:31 -0800, "Charles Huang"
>>> <charles.huang@sybase.com> wrote:
>>>
>>> >Hi
>>> >
>>> >I found that when I insert to a smalldatetime typed column, second value
>>> >loses.
>>> >
>>> >but according to ASA documentation, it's actually timestamp.
>>> >
>>> >just curious, is it a intended design or it's a bug?
>>> >
>>> >
>>> >
>>> >Regards
>>> >
>>> >Charles
>>> >
>>>
>>> --
>>> Breck Carter http://sqlanywhere.blogspot.com/
>>>
>>> RisingRoad SQL Anywhere and MobiLink Professional Services
>>> breck.carter@risingroad.com
>>

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Charles Huang Posted on 2009-04-22 09:18:46.0Z
From: "Charles Huang" <charles.huang@sybase.com>
Newsgroups: sybase.public.sqlanywhere
References: <49af3aaf@forums-1-dub> <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com> <49c06664$1@forums-1-dub> <k731s4530j3go70o0ok2p681j8r95n7up8@4ax.com> <1l31s4phh4vjlnd3o9p87ds466vim2soas@4ax.com>
Subject: Re: smalldatetime issue
Lines: 152
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1933
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1933
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49eee0f6@forums-1-dub>
Date: 22 Apr 2009 02:18:46 -0700
X-Trace: forums-1-dub 1240391926 10.22.241.152 (22 Apr 2009 02:18:46 -0700)
X-Original-Trace: 22 Apr 2009 02:18:46 -0700, vip152.sybase.com
X-Authenticated-User: usecase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:280
Article PK: 866716

Breck

asa version is 11.0.0.1578

and here is the info i extra from jconn3.jar
Name: com/sybase/jdbcx/

Specification-Title: "jConnect for JDBC 3.0"

Implementation-Title: "com.sybase.jdbcx"

Specification-Version: "6.0"

Specification-Vendor: "Sybase, Inc."

Implementation-Version: "Build (26457)"

Implementation-Vendor: "Sybase, Inc."


Charles

"Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com> wrote in
message news:1l31s4phh4vjlnd3o9p87ds466vim2soas@4ax.com...
> Wait, you did say "jconn3"... not enough coffee yet :)
>
> What version of SQL Anywhere?
>
> Breck
>
> On 17 Mar 2009 22:01:43 -0800, "Breck Carter [TeamSybase]"
> <NOSPAM__breck.carter@gmail.com> wrote:
>
> >What version of SQL Anywere are you using? What JDBC driver are you
> >using?
> >
> >Glenn Paulley has begun a discussion of the differences between
> >jConnect and the (better, IMO) iAnywhere JDBC driver here:
>
>http://iablog.sybase.com/paulley/2009/03/differences-between-jconnect-and-t
he-ianywhere-jdbc-driver-part-un/
> >
> >I have exactly one client who prefers jConnect, the rest prefer the
> >iAnywhere driver. And I'm not sure that one client still hold the same
> >opinion.
> >
> >Breck
> >
> >On 17 Mar 2009 19:11:32 -0800, "Charles Huang"
> ><charles.huang@sybase.com> wrote:
> >
> >>maybe it's jconn3 issue, we are using jdbc to get data from asa.
> >>
> >>charles
> >>
> >>"Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com> wrote in
> >>message news:njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com...
> >>> Not a bug, especially since it does NOT drop the seconds value; you
> >>> are probably seeing a client-side or application program or interface
> >>> issue.
> >>>
> >>> Here is the story: There is no such thing as the "smalldatetime" data
> >>> type in SQL Anywhere, in spite of the fact the Help calls it a "data
> >>> type". There is something called the "smalldatetime" *domain*, and it
> >>> is there only for backward compatibility with applications that use
> >>> Transact SQL.
> >>>
> >>> If you are not seeing seconds, then one of two things is happening:
> >>> Your code is truncating the value before storing it, or your code is
> >>> truncating the value when retrieving it. By "your code" I mean
> >>> anything that is not inside the database engine; it could be the
> >>> interface software, the development tools, the class library you're
> >>> using, whatever. Something is pretending that smalldatetime is really
> >>> small :)
> >>>
> >>> Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that by
> >>> default timestamps are returned with seconds to three decimal places,
> >>> but you can change that to six, and it ALWAYS stores what you give it
> >>> (up to six decimal places).
> >>>
> >>> CREATE TABLE t (
> >>> pkey INTEGER NOT NULL PRIMARY KEY,
> >>> now SMALLDATETIME NOT NULL );
> >>>
> >>> INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
> >>> COMMIT;
> >>>
> >>> SELECT * FROM t;
> >>>
> >>> pkey,now
> >>> 1,'2009-03-05 06:04:58.123'
> >>>
> >>> SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';
> >>>
> >>> SELECT * FROM t;
> >>>
> >>> pkey,now
> >>> 1,'2009-03-05 06:04:58.123456'
> >>>
> >>> =====
> >>>
> >>> FWIW the confusion surrounding domains (I hate them) is exactly the
> >>> reason Foxhound shows the true data types:
> >>>
> >>> -- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print -
> >>> Foxhound © 2007 RisingRoad
> >>>
> >>> CREATE TABLE DBA.t ( -- empty
> >>> pkey /* PK */ INTEGER NOT NULL,
> >>> now TIMESTAMP NOT NULL, -- domain smalldatetime
> >>> CONSTRAINT ASA77 PRIMARY KEY (
> >>> pkey )
> >>> );
> >>>
> >>> Breck
> >>>
> >>>
> >>> On 4 Mar 2009 18:36:31 -0800, "Charles Huang"
> >>> <charles.huang@sybase.com> wrote:
> >>>
> >>> >Hi
> >>> >
> >>> >I found that when I insert to a smalldatetime typed column, second
value
> >>> >loses.
> >>> >
> >>> >but according to ASA documentation, it's actually timestamp.
> >>> >
> >>> >just curious, is it a intended design or it's a bug?
> >>> >
> >>> >
> >>> >
> >>> >Regards
> >>> >
> >>> >Charles
> >>> >
> >>>
> >>> --
> >>> Breck Carter http://sqlanywhere.blogspot.com/
> >>>
> >>> RisingRoad SQL Anywhere and MobiLink Professional Services
> >>> breck.carter@risingroad.com
> >>
>
> --
> Breck Carter http://sqlanywhere.blogspot.com/
>
> RisingRoad SQL Anywhere and MobiLink Professional Services
> breck.carter@risingroad.com


Glenn Paulley [Sybase iAnywhere] Posted on 2009-04-03 11:15:28.0Z
From: "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
Subject: Re: smalldatetime issue
References: <49af3aaf@forums-1-dub> <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com>
In-Reply-To: <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49d5efd0$1@forums-1-dub>
Date: 3 Apr 2009 03:15:28 -0800
X-Trace: forums-1-dub 1238757328 10.22.241.152 (3 Apr 2009 03:15:28 -0800)
X-Original-Trace: 3 Apr 2009 03:15:28 -0800, vip152.sybase.com
Lines: 116
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:275
Article PK: 866713

If one uses jConnect, you're using Sybase's TDS wire protocol
underneath, rather than the native iAnywhere wire protocol.

Because of the encoding of timestamp values that TDS uses, it is
incapable of retaining time precision of anything greater than 1/300
second. Hence, regardless of the setting of the TIMESTAMP_FORMAT option,
attempts to INSERT or FETCH a timestamp value from a jConnect
application will result in truncation of the time portion to 1/300 of a
second.

Glenn

Breck Carter [TeamSybase] wrote:
> Not a bug, especially since it does NOT drop the seconds value; you
> are probably seeing a client-side or application program or interface
> issue.
>
> Here is the story: There is no such thing as the "smalldatetime" data
> type in SQL Anywhere, in spite of the fact the Help calls it a "data
> type". There is something called the "smalldatetime" *domain*, and it
> is there only for backward compatibility with applications that use
> Transact SQL.
>
> If you are not seeing seconds, then one of two things is happening:
> Your code is truncating the value before storing it, or your code is
> truncating the value when retrieving it. By "your code" I mean
> anything that is not inside the database engine; it could be the
> interface software, the development tools, the class library you're
> using, whatever. Something is pretending that smalldatetime is really
> small :)
>
> Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that by
> default timestamps are returned with seconds to three decimal places,
> but you can change that to six, and it ALWAYS stores what you give it
> (up to six decimal places).
>
> CREATE TABLE t (
> pkey INTEGER NOT NULL PRIMARY KEY,
> now SMALLDATETIME NOT NULL );
>
> INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
> COMMIT;
>
> SELECT * FROM t;
>
> pkey,now
> 1,'2009-03-05 06:04:58.123'
>
> SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';
>
> SELECT * FROM t;
>
> pkey,now
> 1,'2009-03-05 06:04:58.123456'
>
> =====
>
> FWIW the confusion surrounding domains (I hate them) is exactly the
> reason Foxhound shows the true data types:
>
> -- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print -
> Foxhound © 2007 RisingRoad
>
> CREATE TABLE DBA.t ( -- empty
> pkey /* PK */ INTEGER NOT NULL,
> now TIMESTAMP NOT NULL, -- domain smalldatetime
> CONSTRAINT ASA77 PRIMARY KEY (
> pkey )
> );
>
> Breck
>
>
> On 4 Mar 2009 18:36:31 -0800, "Charles Huang"
> <charles.huang@sybase.com> wrote:
>
>> Hi
>>
>> I found that when I insert to a smalldatetime typed column, second value
>> loses.
>>
>> but according to ASA documentation, it's actually timestamp.
>>
>> just curious, is it a intended design or it's a bug?
>>
>>
>>
>> Regards
>>
>> Charles
>>
>
> --
> Breck Carter http://sqlanywhere.blogspot.com/
>
> RisingRoad SQL Anywhere and MobiLink Professional Services
> breck.carter@risingroad.com

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport


Charles Huang Posted on 2009-04-22 09:20:59.0Z
From: "Charles Huang" <charles.huang@sybase.com>
Newsgroups: sybase.public.sqlanywhere
References: <49af3aaf@forums-1-dub> <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com> <49d5efd0$1@forums-1-dub>
Subject: Re: smalldatetime issue
Lines: 129
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1933
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1933
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49eee17b$1@forums-1-dub>
Date: 22 Apr 2009 02:20:59 -0700
X-Trace: forums-1-dub 1240392059 10.22.241.152 (22 Apr 2009 02:20:59 -0700)
X-Original-Trace: 22 Apr 2009 02:20:59 -0700, vip152.sybase.com
X-Authenticated-User: usecase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:281
Article PK: 866719

Glenn

my issue is that i'm losing second portion for smalldatetime type, not
milli-second portion.

charles

"Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in message
news:49d5efd0$1@forums-1-dub...
> If one uses jConnect, you're using Sybase's TDS wire protocol
> underneath, rather than the native iAnywhere wire protocol.
>
> Because of the encoding of timestamp values that TDS uses, it is
> incapable of retaining time precision of anything greater than 1/300
> second. Hence, regardless of the setting of the TIMESTAMP_FORMAT option,
> attempts to INSERT or FETCH a timestamp value from a jConnect
> application will result in truncation of the time portion to 1/300 of a
> second.
>
> Glenn
>
> Breck Carter [TeamSybase] wrote:
> > Not a bug, especially since it does NOT drop the seconds value; you
> > are probably seeing a client-side or application program or interface
> > issue.
> >
> > Here is the story: There is no such thing as the "smalldatetime" data
> > type in SQL Anywhere, in spite of the fact the Help calls it a "data
> > type". There is something called the "smalldatetime" *domain*, and it
> > is there only for backward compatibility with applications that use
> > Transact SQL.
> >
> > If you are not seeing seconds, then one of two things is happening:
> > Your code is truncating the value before storing it, or your code is
> > truncating the value when retrieving it. By "your code" I mean
> > anything that is not inside the database engine; it could be the
> > interface software, the development tools, the class library you're
> > using, whatever. Something is pretending that smalldatetime is really
> > small :)
> >
> > Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that by
> > default timestamps are returned with seconds to three decimal places,
> > but you can change that to six, and it ALWAYS stores what you give it
> > (up to six decimal places).
> >
> > CREATE TABLE t (
> > pkey INTEGER NOT NULL PRIMARY KEY,
> > now SMALLDATETIME NOT NULL );
> >
> > INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
> > COMMIT;
> >
> > SELECT * FROM t;
> >
> > pkey,now
> > 1,'2009-03-05 06:04:58.123'
> >
> > SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';
> >
> > SELECT * FROM t;
> >
> > pkey,now
> > 1,'2009-03-05 06:04:58.123456'
> >
> > =====
> >
> > FWIW the confusion surrounding domains (I hate them) is exactly the
> > reason Foxhound shows the true data types:
> >
> > -- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print -
> > Foxhound © 2007 RisingRoad
> >
> > CREATE TABLE DBA.t ( -- empty
> > pkey /* PK */ INTEGER NOT NULL,
> > now TIMESTAMP NOT NULL, -- domain smalldatetime
> > CONSTRAINT ASA77 PRIMARY KEY (
> > pkey )
> > );
> >
> > Breck
> >
> >
> > On 4 Mar 2009 18:36:31 -0800, "Charles Huang"
> > <charles.huang@sybase.com> wrote:
> >
> >> Hi
> >>
> >> I found that when I insert to a smalldatetime typed column, second
value
> >> loses.
> >>
> >> but according to ASA documentation, it's actually timestamp.
> >>
> >> just curious, is it a intended design or it's a bug?
> >>
> >>
> >>
> >> Regards
> >>
> >> Charles
> >>
> >
> > --
> > Breck Carter http://sqlanywhere.blogspot.com/
> >
> > RisingRoad SQL Anywhere and MobiLink Professional Services
> > breck.carter@risingroad.com
>
> --
> Glenn Paulley
> Director, Engineering (Query Processing)
> Sybase iAnywhere
>
> Blog: http://iablog.sybase.com/paulley
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://case-express.sybase.com
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available through the
> Sybase iAnywhere pages at
>

http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport


"Chris Keating [Sybase iAnywhere]" <keating_no Posted on 2009-04-22 11:09:48.0Z
From: "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
Subject: Re: smalldatetime issue
References: <49af3aaf@forums-1-dub> <njcvq4d4pv4gi3d5rrombrtv26tc9pf7av@4ax.com> <49d5efd0$1@forums-1-dub> <49eee17b$1@forums-1-dub>
In-Reply-To: <49eee17b$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49eefafc@forums-1-dub>
Date: 22 Apr 2009 04:09:48 -0700
X-Trace: forums-1-dub 1240398588 10.22.241.152 (22 Apr 2009 04:09:48 -0700)
X-Original-Trace: 22 Apr 2009 04:09:48 -0700, vip152.sybase.com
Lines: 133
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:282
Article PK: 866717

Show us an example of what you are seeing with a code snipet (or full
example).

-chris

Charles Huang wrote:
> Glenn
>
> my issue is that i'm losing second portion for smalldatetime type, not
> milli-second portion.
>
> charles
>
> "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in message
> news:49d5efd0$1@forums-1-dub...
>> If one uses jConnect, you're using Sybase's TDS wire protocol
>> underneath, rather than the native iAnywhere wire protocol.
>>
>> Because of the encoding of timestamp values that TDS uses, it is
>> incapable of retaining time precision of anything greater than 1/300
>> second. Hence, regardless of the setting of the TIMESTAMP_FORMAT option,
>> attempts to INSERT or FETCH a timestamp value from a jConnect
>> application will result in truncation of the time portion to 1/300 of a
>> second.
>>
>> Glenn
>>
>> Breck Carter [TeamSybase] wrote:
>>> Not a bug, especially since it does NOT drop the seconds value; you
>>> are probably seeing a client-side or application program or interface
>>> issue.
>>>
>>> Here is the story: There is no such thing as the "smalldatetime" data
>>> type in SQL Anywhere, in spite of the fact the Help calls it a "data
>>> type". There is something called the "smalldatetime" *domain*, and it
>>> is there only for backward compatibility with applications that use
>>> Transact SQL.
>>>
>>> If you are not seeing seconds, then one of two things is happening:
>>> Your code is truncating the value before storing it, or your code is
>>> truncating the value when retrieving it. By "your code" I mean
>>> anything that is not inside the database engine; it could be the
>>> interface software, the development tools, the class library you're
>>> using, whatever. Something is pretending that smalldatetime is really
>>> small :)
>>>
>>> Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that by
>>> default timestamps are returned with seconds to three decimal places,
>>> but you can change that to six, and it ALWAYS stores what you give it
>>> (up to six decimal places).
>>>
>>> CREATE TABLE t (
>>> pkey INTEGER NOT NULL PRIMARY KEY,
>>> now SMALLDATETIME NOT NULL );
>>>
>>> INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
>>> COMMIT;
>>>
>>> SELECT * FROM t;
>>>
>>> pkey,now
>>> 1,'2009-03-05 06:04:58.123'
>>>
>>> SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';
>>>
>>> SELECT * FROM t;
>>>
>>> pkey,now
>>> 1,'2009-03-05 06:04:58.123456'
>>>
>>> =====
>>>
>>> FWIW the confusion surrounding domains (I hate them) is exactly the
>>> reason Foxhound shows the true data types:
>>>
>>> -- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print -
>>> Foxhound © 2007 RisingRoad
>>>
>>> CREATE TABLE DBA.t ( -- empty
>>> pkey /* PK */ INTEGER NOT NULL,
>>> now TIMESTAMP NOT NULL, -- domain smalldatetime
>>> CONSTRAINT ASA77 PRIMARY KEY (
>>> pkey )
>>> );
>>>
>>> Breck
>>>
>>>
>>> On 4 Mar 2009 18:36:31 -0800, "Charles Huang"
>>> <charles.huang@sybase.com> wrote:
>>>
>>>> Hi
>>>>
>>>> I found that when I insert to a smalldatetime typed column, second
> value
>>>> loses.
>>>>
>>>> but according to ASA documentation, it's actually timestamp.
>>>>
>>>> just curious, is it a intended design or it's a bug?
>>>>
>>>>
>>>>
>>>> Regards
>>>>
>>>> Charles
>>>>
>>> --
>>> Breck Carter http://sqlanywhere.blogspot.com/
>>>
>>> RisingRoad SQL Anywhere and MobiLink Professional Services
>>> breck.carter@risingroad.com
>> --
>> Glenn Paulley
>> Director, Engineering (Query Processing)
>> Sybase iAnywhere
>>
>> Blog: http://iablog.sybase.com/paulley
>>
>> EBF's and Patches: http://downloads.sybase.com
>> choose SQL Anywhere Studio >> change 'time frame' to all
>>
>> To Submit Bug Reports: http://case-express.sybase.com
>>
>> SQL Anywhere Studio Supported Platforms and Support Status
>> http://my.sybase.com/detail?id=1002288
>>
>> Whitepapers, TechDocs, and bug fixes are all available through the
>> Sybase iAnywhere pages at
>>
> http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport
>
>