Need help on derived table

[Former Member]

Hi Experts,

When I create the derived table, I got this error message.

Exception: DBD, [Microsoft][ODBC SQL Server Driver]Communication link failureState:08S01

But I can run the query in SQLServer management Studio successfully.

What kind of syntax I should follow?

The following is the script:

select INV. docdate as 'Date', sum(INV.doctotal) as 'Total' from OINV INV

where INV.docdate >= dateadd(day, -14,convert(date,getdate())) and INV.docdate < dateadd(day, 1,convert(date,getdate()))

group by INV.docdate

union

select CR.docdate as 'Date', sum(CR.doctotal*-1) as 'Total' from ORIN CR

where CR.docdate >= dateadd(day, -14,convert(date,getdate())) and CR.docdate < dateadd(day, 1,convert(date,getdate()))

group by CR.docdate

Derived table cannot recognize dateadd() ?

Thanks,

Christina

[Former Member]

HI,

I have a question in your query

dateadd(day, -14,convert(date,getdate()))

1st thing is getdate() is of date type data type, why do you want to convert it into date again. Are you looking for specifice format over there?

But "Date" is not data type that will be accepted by sql server. you can use "datetime" instead

select dateadd(day, -14,convert(datetime,getdate()))

If you want to convert the getdate into some specific format then you have set of parameters to do that

you can get information from the following link

http://www.sql-server-helper.com/tips/date-formats.aspx

Another thing is try to copy the derived table query and run it in sql db directly, which will be easy to fix the issues based on the error descriptions

Hope this helps