Openquery oracle date selection question.

[saustin]

Hi,
Have a question about formatting the date variable as a criteria in an openquery oracle query. No problem returning data very quickly if the date criteria is not used. Here is an example of the q.REQUEST_DATE data "2005-10-03 00:00:00.000"

Declare @BEGINDATE datetime
Declare @ENDDATE datetime
Declare @query varchar(8000)
Declare @qSTATUS varchar(1)

set @qSTATUS='A'
set @BEGINDATE='10/26/2005'
set @ENDDATE='12/31/2005'

set @query = 'SELECT * FROM OPENQUERY(VMFG, ''SELECT q.STATUS, q.ID, q.REQUEST_DATE, q.CREATE_DATE, l.PART_ID,
l.DESCRIPTION, l.PURCHASE_UM, l.QTY_BREAK_1, l.QTY_BREAK_2, l.QTY_BREAK_3, l.QTY_BREAK_4,
l.QTY_BREAK_5, l.QTY_BREAK_6, l.QTY_BREAK_7, l.QTY_BREAK_8, l.QTY_BREAK_9, l.QTY_BREAK_10,
v.VENDOR_ID, v.NAME
FROM REQUEST_FOR_QUOTE q, RFQ_LINE l, RFQ_VENDOR v
WHERE q.STATUS=''''' + @qSTATUS + ''''' AND q.ID = l.RFQ_ID AND q.ID = v.RFQ_ID AND q.REQUEST_DATE BETWEEN ''''' + @BEGINDATE + ''''' And ''''' + @ENDDATE+ ''''' ORDER BY v.NAME, q.REQUEST_DATE'')'
exec(@query)


Changing the above to .....

Declare @BEGINDATE datetime
Declare @ENDDATE datetime
Declare @query varchar(8000)
Declare @qSTATUS varchar(1)

set @qSTATUS='A'
set @BEGINDATE='2005-10-03 00:00:00.000'
set @ENDDATE='12/31/2005'

set @query = 'SELECT * FROM OPENQUERY(VMFG, ''SELECT q.STATUS, q.ID, q.REQUEST_DATE, q.CREATE_DATE, l.PART_ID,
l.DESCRIPTION, l.PURCHASE_UM, l.QTY_BREAK_1, l.QTY_BREAK_2, l.QTY_BREAK_3, l.QTY_BREAK_4,
l.QTY_BREAK_5, l.QTY_BREAK_6, l.QTY_BREAK_7, l.QTY_BREAK_8, l.QTY_BREAK_9, l.QTY_BREAK_10,
v.VENDOR_ID, v.NAME
FROM REQUEST_FOR_QUOTE q, RFQ_LINE l, RFQ_VENDOR v
WHERE q.STATUS=''''' + @qSTATUS + ''''' AND q.ID = l.RFQ_ID AND q.ID = v.RFQ_ID AND q.REQUEST_DATE=''''' + @BEGINDATE + ''''' ORDER BY v.NAME, q.REQUEST_DATE'')'
exec(@query)

produces the 'Syntax error converting datetime from character string.' Does anyone have a way around this ?

Many thanks, Steve.

 

[vongrunt]

First query also crashes with same error message to me...

Datetime has higher precedence from varchar. CONVERT() @BEGINDATE and @ENDDATE to varchar(10) before concatenation to @query.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[saustin]

Pardon, sorry, forgot to remove the .....
AND q.REQUEST_DATE BETWEEN ''''' + @BEGINDATE + ''''' And ''''' + @ENDDATE+ ''''' .....

Have been doing so much testing.

Am now trying the TO_DATE() function.

 

[vongrunt]

Again: datatime has higher precedence that varchar. To illustrate:

declare @query varchar(8000)
declare @date datetime; set @date = '12/31/2005'
set @query = 'select * from blah where foo=''' + @date + ''''

Error happens because SQL Server actually attempts to convert 'select * from ....' (varchar) to @date (datetime, higher precedence).

To avoid that error, explicitely convert date variable to varchar, for example:

set @query = 'select * from blah where foo=''' + [b]convert(varchar(10),[/b] @date[b], 101)[/b] + ''''

Note that expression you build must have syntax and dates in format recognizable by Oracle.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[saustin]

solved this with correct to_date syntax. Many thanks for your reply vongrunt !