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 parts identifiers from record

3 posts in General Discussion Last posting was on 2010-01-11 14:51:50.0Z
Claus Scherschel Posted on 2010-01-11 11:55:36.0Z
Sender: 1db2.4b4b1153.1804289383@sybase.com
From: Claus Scherschel
Newsgroups: ianywhere.public.general
Subject: date parts identifiers from record
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b4b11b8.1dbb.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Jan 2010 03:55:36 -0800
X-Trace: forums-1-dub 1263210936 10.22.241.41 (11 Jan 2010 03:55:36 -0800)
X-Original-Trace: 11 Jan 2010 03:55:36 -0800, 10.22.241.41
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7870
Article PK: 6905

Hi there

This is driving me nuts: a database driven planning tool
stores
intervals (integer) and the date parts (varchar) provided by
the user.
Now I would like to do some date calculations (e.g. adding
the
interval to a given date). But DATEADD doesn't take string
expressions as date parts, not even when allowing quoted
identifiers.
So
select DATEADD(tbl.mydatepart,tbl.myinterval,'2010/01/11')
from
mytable tbl
throws an error.
Till now, date calculations were done in application, but
now I'll have to do them in views.

How could I handle this? Thanks for your help!

Best regards
Claus.


Glenn Paulley [Sybase iAnywhere] Posted on 2010-01-11 14:19:07.0Z
From: "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: date parts identifiers from record
References: <4b4b11b8.1dbb.1681692777@sybase.com>
In-Reply-To: <4b4b11b8.1dbb.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b4b335b@forums-1-dub>
Date: 11 Jan 2010 06:19:07 -0800
X-Trace: forums-1-dub 1263219547 10.22.241.152 (11 Jan 2010 06:19:07 -0800)
X-Original-Trace: 11 Jan 2010 06:19:07 -0800, vip152.sybase.com
Lines: 58
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7871
Article PK: 6903

As you noted, because DATEADD's first parameter is a keyword, rather
than a string, this makes query construction a bit more difficult in
your case. There is always the brute force (untested)

CASE tbl.mydatepart
WHEN 'MONTH' THEN DATEADD( MONTH, tbl.myinterval, <date string>)
WHEN 'YEAR' THEN DATEADD( YEAR, tbl.myinterval, <date string>)
OTHERWISE NULL
END

or constructing the query piecemeal via EXECUTE IMMEDIATE - though this
won't work with trying to embody the logic in a view.

Glenn

Claus Scherschel wrote:
> Hi there
>
> This is driving me nuts: a database driven planning tool
> stores
> intervals (integer) and the date parts (varchar) provided by
> the user.
> Now I would like to do some date calculations (e.g. adding
> the
> interval to a given date). But DATEADD doesn't take string
> expressions as date parts, not even when allowing quoted
> identifiers.
> So
> select DATEADD(tbl.mydatepart,tbl.myinterval,'2010/01/11')
> from
> mytable tbl
> throws an error.
> Till now, date calculations were done in application, but
> now I'll have to do them in views.
>
> How could I handle this? Thanks for your help!
>
> Best regards
> Claus.

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


Claus Scherschel Posted on 2010-01-11 14:51:50.0Z
Sender: 1db2.4b4b1153.1804289383@sybase.com
From: Claus Scherschel
Newsgroups: ianywhere.public.general
Subject: Re: date parts identifiers from record
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b4b3b06.22df.1681692777@sybase.com>
References: <4b4b335b@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Jan 2010 06:51:50 -0800
X-Trace: forums-1-dub 1263221510 10.22.241.41 (11 Jan 2010 06:51:50 -0800)
X-Original-Trace: 11 Jan 2010 06:51:50 -0800, 10.22.241.41
Lines: 17
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7872
Article PK: 6904

Hi!

> always the brute force (untested)
>
> CASE tbl.mydatepart
> WHEN 'MONTH' THEN DATEADD( MONTH, tbl.myinterval, <date
> string>) WHEN 'YEAR' THEN DATEADD( YEAR, tbl.myinterval,
> <date string>) OTHERWISE NULL
> END

Yes, that's the solution I came up with in the meanwhile. I
wrote alternative functions for dateadd and datediff (the
ones needed most in my case) taking the date part as string.
I was hoping for a more elegant way to do that.

Thanks anyway!
Claus.