I am trying to execute the following query:
SELECT *
FROM OPENQUERY
(
CLP,
'
SELECT *
FROM ORACLE_TABLE
WHERE [UPDATEDATE] > ''1900-01-01 12:00 AM''
'
)
This query works fine when I remove the date criteria. But as soon as I try to pass this criteria it no longer works. I can't figure out what I am missing.
Try to remove [and] and add convert date:
SELECT *
FROM OPENQUERY
(CLP,
'
SELECT *
FROM ORACLE_TABLE
WHERE
UPDATEDATE > to_date(''1900-01-01 12:00'',''yyyy-mm-dd hh:mi'')
'
)
or with am
SELECT *
FROM OPENQUERY
(CLP,
'
SELECT *
FROM ORACLE_TABLE
WHERE
UPDATEDATE > to_date(''1900-01-01 12:00 AM'',''yyyy-mm-dd hh:miam '')
'
)
UPDATEDATE column is of DATE datatype (which probably is the case)? it would probably be OK if the column is varchar2.select to_date(sysdate,'yyyy-mm-dd hh:miam') from dual; and check for yourself. It will entirely depend on the NLS date parameter you have!TO_CHAR would make more sense than TO_DATE. Like select to_char(sysdate,'yyyy-mm-dd hh:mi am') from dual;.
TO_DATE around a varchar makes more sense i.e. '1900-01-01 12:00' is a character string (varchar) field.Use
SELECT *
FROM OPENQUERY
(CLP,SELECT * FROM ORACLE_TABLE WHERE trunc(UPDATEDATE) > ''01-JAN-1900'')
All dates with no time component on them defaults to 12:00 AM (or 00:00 Hrs) in Oracle.
You can also use to_timestamp(UPDATEDATE) but for this to work the column should be of timestamp type (i.e. contain timestamp on it otherwise it would always give 12 AM). You can also use to_char(UPDATEDATE,'YYYY-MM-DD HH:MI AM').