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.

Claus,

it might be helpfull if you give some more information: SQLA version,
error message and the values of tbl.mydatepart and tbl.myinterval.

The first parameter of DATEADD is a constant (not a string!). So you
might want to compose a string for the statement and use execute
immediate to get the result (or EXECUTE (...) if you're coding T-SQL).

Regards
Reimer

Claus wrote:

Use a CASE expression as in the following example:

select
( case mydatepart
when 'year' then DATEADD(year,myinterval,'2010/01/11')
when 'quarter' then DATEADD(quarter,myinterval,'2010/01/11')
when 'month' then DATEADD(month,myinterval,'2010/01/11')
when 'week' then DATEADD(week,myinterval,'2010/01/11')
when 'day' then DATEADD(day,myinterval,'2010/01/11')
when 'dayofyear' then DATEADD(dayofyear,myinterval,'2010/01/11')
when 'hour' then DATEADD(hour,myinterval,'2010/01/11')
when 'minute' then DATEADD(minute,myinterval,'2010/01/11')
when 'second' then DATEADD(second,myinterval,'2010/01/11')
when 'millisecond' then
DATEADD(millisecond,myinterval,'2010/01/11')
end
) as SomeVal
FROM T;


R. Pods wrote:

On 11 Jan., 15:34, "Chris Keating (Sybase iAnywhere)"
<keating_nos... (AT) sybase (DOT) com> wrote:
Yes, that's what I came up with, too. It's a bit one the brute-force
side, but anyway... Thanks a lot!

So, why the hell is it using constants? Would they be replaceable by
integers?

Claus.

DATEADD() was originally a Transact-SQL function, and it's included in
SQL Anywhere for compatibility with Sybase ASE and Microsoft SQL Server.
DATEADD() in SQL Anywhere is defined identically with those two
products. Here is the link

http://msdn.microsoft.com/en-us/library/ms186819.aspx

to the Microsoft documentation that describes DATEADD().

Glenn

Claus wrote:
--
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/datab...chnicalsupport