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.

Enable date and time data types

5 posts in General Discussion Last posting was on 2011-04-29 22:17:40.0Z
Héctor Corzo Posted on 2011-04-14 21:37:41.0Z
Sender: 1808.4da75724.1804289383@sybase.com
From: Héctor Corzo
Newsgroups: sybase.public.ase.general
Subject: Enable date and time data types
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4da76925.19af.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Apr 2011 14:37:41 -0700
X-Trace: forums-1-dub 1302817061 10.22.241.41 (14 Apr 2011 14:37:41 -0700)
X-Original-Trace: 14 Apr 2011 14:37:41 -0700, 10.22.241.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30109
Article PK: 79337

Good afternoon.

We need to use the time and date types added in ASE 12.5.1
in a existent database schema. Below the results of querying
systypes in master and finance (the schema in which we need
the additional data types)

use master
go

select name from systypes where name in ('time','date')

use finance
go

select name from systypes where name in ('time','date')


name
------------------------------
date
time

(2 row(s) affected)

name
------------------------------

(0 row(s) affected)

What can we do in order to enable the date and time data
types in my finance schema?. Thanks in advance.

Héctor
Edit/Delete Message


Rob V [ Sybase ] Posted on 2011-04-14 22:10:03.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Enable date and time data types
References: <4da76925.19af.1681692777@sybase.com>
In-Reply-To: <4da76925.19af.1681692777@sybase.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: <4da770bb$1@forums-1-dub>
Date: 14 Apr 2011 15:10:03 -0700
X-Trace: forums-1-dub 1302819003 10.22.241.152 (14 Apr 2011 15:10:03 -0700)
X-Original-Trace: 14 Apr 2011 15:10:03 -0700, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30110
Article PK: 79340


On 14-Apr-2011 23:37, Héctor Corzo wrote:
> select name from systypes where name in ('time','date')

Which ASE version are you running?
If 12.5.1 or later, then date and time will be in systypes.
If you are on 12.5.1+ but they are *not* in systypes in obe database but
not in another (as your first query seems to suggest), then something is
badly wrong, and you should consider recreating your server from scratch.

The only reasonable explanation I can think of why these datatypes would
not exist in one database but they do exist in another, is that you're
on pre-12.5.1, but someone create user-defined datatypes named 'date'
and 'time' in the latter database (since their names show up in systypes
too).

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Bret Halford Posted on 2011-04-18 18:02:55.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Enable date and time data types
References: <4da76925.19af.1681692777@sybase.com> <4da770bb$1@forums-1-dub>
In-Reply-To: <4da770bb$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: <4dac7ccf$1@forums-1-dub>
Date: 18 Apr 2011 11:02:55 -0700
X-Trace: forums-1-dub 1303149775 10.22.241.152 (18 Apr 2011 11:02:55 -0700)
X-Original-Trace: 18 Apr 2011 11:02:55 -0700, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30127
Article PK: 79355


On 4/14/2011 4:10 PM, Rob V [ Sybase ] wrote:
> On 14-Apr-2011 23:37, Héctor Corzo wrote:
>> select name from systypes where name in ('time','date')
>
> Which ASE version are you running?
> If 12.5.1 or later, then date and time will be in systypes.
> If you are on 12.5.1+ but they are *not* in systypes in obe database but
> not in another (as your first query seems to suggest), then something is
> badly wrong, and you should consider recreating your server from scratch.
>
> The only reasonable explanation I can think of why these datatypes would
> not exist in one database but they do exist in another, is that you're
> on pre-12.5.1, but someone create user-defined datatypes named 'date'
> and 'time' in the latter database (since their names show up in systypes
> too).
>

It might not be badly wrong if the database without these types
had them as user-defined types prior to the upgrade and they were then
dropped.

Is the server case-sensitive, and if so, do you have any
variations of "date" or "time" in systypes containing uppercase letters
(Date, DATE, etc).

12.5.1 introduced new date and time datatypes. The 12.5.x upgrade
process checks to see if there are existing user-defined types with the
same names, and if so, doesn't add the new system types (which are then
unavailable unless manually added at a later time).

Starting in 15.0, user defined datatypes named date or time are no
longer allowed.

Anyway, you should be able to manually copy the rows from master
into your database. Take the usual precautions of trying it on
a test server if possible and taking a backup dump just before doing
it on production.

-bret