Topic
  • 1 reply
  • Latest Post - ‏2012-05-14T08:37:43Z by Arvind_Gupta
RoscoeFinny
RoscoeFinny
1 Post

Pinned topic adding duration to date in SqlDataSource loading GridView

‏2012-05-11T18:46:44Z |
I'm building a sql query in the code behind of an aspx page and loading the query into a session variable. The next aspx page presented has some code behind that accesses the session variable and makes it the SelectCommand for a SqlDataSource loading a GridView. Bit cumbersome but that part works. Problem is with the sql statement failing, throwing exception...

SQL0187: Use of labeled duration not valid.
Cause . . . . . : One of the following has occurred: -- A labeled duration is specified but is not the operand of the operators plus or minus. -- A labeled duration of years, months, or days is specified as the operand of addition or subtraction and the other operand is not date or timestamp. -- A labeled duration of hours, minutes, or seconds is specified as the operand of addition or subtraction and the other operand is not time or timestamp. -- A labeled duration of microseconds is specified as the operand of addition or subtraction and the other operand is not timestamp. -- A labeled duration is specified as the left operand of subtraction. -- The value specified for the labeled duration is not a numeric type. Recovery . . . : Correct the use of the labeled duration. Try the request again.

...the substitution of the session variable into the query looks like this in the code...

...where t2.sordte>=('" + Session("StartDate") + "' + 3 DAYS) and...

...the failure being on the corresponding bit of the query that when the string is interpreted looks like (from the debug viewer)...

...where t2.sordte>=('04/20/2012' + 3 DAYS) and ...

...which I know is the failure point because when I change the code to make it be...

...where t2.sordte>='04/20/2012' and ...

...the query works.

Can someone show me what the correct syntax should be? Or if I'm just doing it wrong show me how it should be done? I think it has something to do with the DB2 syntax there being no addduration.

Any help much appreciated, Roscoe
Updated on 2012-05-14T08:37:43Z at 2012-05-14T08:37:43Z by Arvind_Gupta
  • Arvind_Gupta
    Arvind_Gupta
    58 Posts

    Re: adding duration to date in SqlDataSource loading GridView

    ‏2012-05-14T08:37:43Z  
    Hi,

    You need to use DATE() function in your query, e.g. select * from Employee where HIREDATE = (DATE('10/06/2003') + 4 DAYS);

    You need to change your query as
    ...where t2.sordte>=(*DATE*('04/20/2012') + 3 DAYS) and ...
    Please refer DB2 documentation for detailed info
    http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0023457.html

    Thanks,
    Arvind