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
Pinned topic adding duration to date in SqlDataSource loading GridView
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-05-14T08:37:43Z at 2012-05-14T08:37:43Z by Arvind_Gupta
Arvind_Gupta 2700025SKH58 Posts
Re: adding duration to date in SqlDataSource loading GridView2012-05-14T08:37:43ZThis is the accepted answer. This is the accepted answer.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