openquery: datum formatting

Question

I'm trying to retrieve data from Oracle by SQL server so I'm using openquery and I'm able to get date interval this way:

SELECT
  *
FROM OPENQUERY(DATABASE, 
     'SELECT * FROM ORACLE.TABLE
      WHERE CREATEDATE between {d ''2013-03-23''} and {d ''2013-03-27''} ')

how can I do the similar query, but date interval will be in yyyy-mm-dd hh:mm:ss format and stay with openquery???

score 1 · Accepted Answer · answered Mar 27 '13 at 13:46

1

You could do the conversion from date to string in the openquery statement:

select to_char(<datecol>, 'YYYY-MM-DD hh:mi:ss') . . .

Alternatively, you culd use convert() in SQL Server and do it in the outer select.

answered Mar 27 '13 at 13:46

Gordon Linoff

Your solution works, thanks, but i dont't know why, the solution I've looking for (and found today): `CREATEDATE between {ts ''2013-03-23 00:00:00''} and {ts ''2013-03-27 23:59:59''}` seems to be much faster... – gaffcz Mar 28 '13 at 08:20

1 Answers1