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.

Date and Time Format

2 posts in OLEDB Last posting was on 2012-03-09 16:41:36.0Z
benedictum Posted on 2012-02-17 13:45:26.0Z
From: "Benedictum" <dominus@vobis.cum>
Subject: Date and Time Format
Newsgroups: Advantage.OLEDB
User-Agent: XanaNews/1.18.1.6
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 68.255.221.194
Message-ID: <4f3e6806$1@solutions.advantagedatabase.com>
Date: 17 Feb 2012 06:45:26 -0700
X-Trace: 17 Feb 2012 06:45:26 -0700, 68.255.221.194
Lines: 14
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.OLEDB:1377
Article PK: 1133894

I am using ADS9.1 OleDB connection. I need to extract date only from a
datatime field (ADT TimeStamp). I have used the following SQL
statement: "SELECT CONVERT(starttime,SQL_DATE), CONVERT(starttime,
SQL_TIME) FROM events"

In the date, I get "2/12/2012 12:00:00 AM" - how do I get the date
without the time string?
With the time I get "14:30:00" - how do I get something like "2:30 PM"
Help is appreciated.
---B
--


Mark Wilkins Posted on 2012-03-09 16:41:36.0Z
From: "Mark Wilkins" <a@b.c>
Newsgroups: Advantage.OLEDB
References: <4f3e6806$1@solutions.advantagedatabase.com>
In-Reply-To: <4f3e6806$1@solutions.advantagedatabase.com>
Subject: Re: Date and Time Format
Date: Fri, 9 Mar 2012 09:41:36 -0700
Lines: 1
Organization: Sybase
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8089.726
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8089.726
NNTP-Posting-Host: 10.6.193.219
Message-ID: <4f5a328d$1@solutions.advantagedatabase.com>
X-Trace: 9 Mar 2012 08:40:45 -0700, 10.6.193.219
Path: solutions.advantagedatabase.com
Xref: solutions.advantagedatabase.com Advantage.OLEDB:1378
Article PK: 1133896

Hi,

The SQL statement as shown should be returning DBTYPE_DBDATE and
DBTYPE_DBTIME values (the OLE DB types). The data that gets returned is in
a very specific format according to the OLE DB spec. The actual display of
the data would be dependent on the application (and its development
environment/IDE). So it appears that the application itself is interpreting
the date value as a timestamp.

If you wanted to return the data as character, the SQL statement could maybe
be changed so that it does not even return date and time types:

SELECT CONVERT(CONVERT(starttime,SQL_DATE), SQL_CHAR), ...

However, I think that would format the values according to SQL ANSI style
dates (2012-02-12 and 14:30:00).

Mark Wilkins
Advantage R&D

"Benedictum" <dominus@vobis.cum> wrote in message
news:4f3e6806$1@solutions.advantagedatabase.com...
>
> I am using ADS9.1 OleDB connection. I need to extract date only from a
> datatime field (ADT TimeStamp). I have used the following SQL
> statement: "SELECT CONVERT(starttime,SQL_DATE), CONVERT(starttime,
> SQL_TIME) FROM events"
>
> In the date, I get "2/12/2012 12:00:00 AM" - how do I get the date
> without the time string?
> With the time I get "14:30:00" - how do I get something like "2:30 PM"
> Help is appreciated.
> ---B
> --
>
>