How can I hardcode date into Oracle query in SSIS

Question

I am very new to Oracle database and I am trying to hardcode the date (2020-06-30 0:00:00) into the query below, however I get the following errors

[valsys_TIMESERIES_VALUE [37]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ORA-12801: error signaled in parallel query server P006 ORA-01821: date format not recognized

ORA-02063: preceding 2 lines from VALSYS". An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12801: error signaled in parallel query server P006

ORA-01821: date format not recognized ORA-02063: preceding 2 lines from VALSYS".

"SELECT TS_ID,DATE_UTC,VALUE,CASE WHEN VALUE = 0 THEN '0' ELSE TO_CHAR(VALUE) END VALUE_CONV ,SUBSTITUTE_VALUE
,CASE WHEN SUBSTITUTE_VALUE = 0 THEN '0' ELSE TO_CHAR(SUBSTITUTE_VALUE) END SUBSTITUTE_VALUE_CONV ,MANUAL_VALUE, CASE WHEN MANUAL_VALUE = 0 THEN '0' ELSE TO_CHAR(MANUAL_VALUE) END MANUAL_VALUE_CONV,FEASIBLE
,VERIFIED,APPROVED,VALID_FROM,VALID_UNTIL,LAST_EXPORT,DAY_CET,COMPUTED,MARKER,TASK_UNIT_ID FROM  \""+ @[$Project::Oracle_Valsys_Schemaname] + "\".\"VALSYS_TIMESERIES_VALUE\" 
WHERE VALID_FROM >  to_timestamp('"+  @[User::PreLET]  + "', '2020-06-30 0:00:00') "

You can use a timestamp or date literal, e.g. WHERE valid_from > timestamp '2020-06-30 00:00:00' (the hour must be two digits) or WHERE valid_from > date '2020-06-30'. (And if valid_from is not a date, but a datetime, then I'd rather expect >= instead of >, so as to not exclude midnight. What does @[User::PreLET] contain? The to_timestamp should have a date format for the second parameter., e.g. 'yyyy.mm.dd hh24:mi:ss'. — Thorsten Kettner

Marmite Bomber Marmite Bomber · Accepted Answer · 2020-09-10T13:03:34

The second parameter of to_timestamp is a format mask, e.g. 'yyyy-mm-dd hh24:mi:ss'

This is a sample usage

select 
  to_timestamp('2020-06-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') tst
from dual;  

TST                          
-----------------------------
30.06.2020 00:00:00,000000000

How can I hardcode date into Oracle query in SSIS

1 Answers