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.

Convert Function

2 posts in General Discussion Last posting was on 2004-03-17 18:05:00.0Z
elaviana Posted on 2004-03-17 17:22:33.0Z
Sender: 4a0a.40588924.1804289383@sybase.com
From: elaviana
Newsgroups: ianywhere.public.general
Subject: Convert Function
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40588958.4a0e.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Mar 2004 09:22:33 -0800
X-Trace: forums-1-dub 1079544153 10.22.241.41 (17 Mar 2004 09:22:33 -0800)
X-Original-Trace: 17 Mar 2004 09:22:33 -0800, 10.22.241.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2793
Article PK: 6264

I have the following SQL select statement where TRANS_DATE
is a datetime column

SELECT DISTINCT SK, TABLE_NAME, ACTION_TYPE, TRANS_DATE FROM
GMI..ZH_HISTORY WHERE PROGRAM_NAME != 'MDAL' AND (TABLE_NAME
= 'TGT_LIST' OR TABLE_NAME = 'TGT_LIST_TIE' OR TABLE_NAME =
'TGT_LIST_TIE_ORDER' OR TABLE_NAME =
'TGT_LIST_TIE_ORDER_TIE') AND convert(varchar(27),
TRANS_DATE, 109) BETWEEN 'Mar 12 2004 11:37:51:499PM' AND
'Mar 12 2004 11:45:59:981PM' ORDER BY TRANS_DATE

The result includes entries in the time period above but it
ignores the "PM" restriction. In another words it returns a
record with a time of "Mar 12 2004 11:41:55:901AM"

Any suggestions ?


Breck Carter [TeamSybase] Posted on 2004-03-17 18:05:00.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Convert Function
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <6l4h5098kasoje3nooeufb0n13eq1slv4v@4ax.com>
References: <40588958.4a0e.846930886@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 17 Mar 2004 10:04:57 -0800, bcarter.sentex.ca
Lines: 29
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 17 Mar 2004 10:04:58 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 17 Mar 2004 10:05:00 -0800
X-Trace: forums-1-dub 1079546700 10.22.108.75 (17 Mar 2004 10:05:00 -0800)
X-Original-Trace: 17 Mar 2004 10:05:00 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2796
Article PK: 6269

It is working just fine for a left-to-right string comparison, which is what you are asking for.
Stop converting the string and just compare timestamps, or use the 24-hour clock when converting to
a string.

On 17 Mar 2004 09:22:33 -0800, elaviana wrote:

>I have the following SQL select statement where TRANS_DATE
>is a datetime column
>
>SELECT DISTINCT SK, TABLE_NAME, ACTION_TYPE, TRANS_DATE FROM
>GMI..ZH_HISTORY WHERE PROGRAM_NAME != 'MDAL' AND (TABLE_NAME
>= 'TGT_LIST' OR TABLE_NAME = 'TGT_LIST_TIE' OR TABLE_NAME =
>'TGT_LIST_TIE_ORDER' OR TABLE_NAME =
>'TGT_LIST_TIE_ORDER_TIE') AND convert(varchar(27),
>TRANS_DATE, 109) BETWEEN 'Mar 12 2004 11:37:51:499PM' AND
>'Mar 12 2004 11:45:59:981PM' ORDER BY TRANS_DATE
>
>The result includes entries in the time period above but it
>ignores the "PM" restriction. In another words it returns a
>record with a time of "Mar 12 2004 11:41:55:901AM"
>
>Any suggestions ?

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com