How can I hardcode date into Oracle query in SSIS

0

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') "
oracle
date
timestamp

1 Answer

0

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
answered on Stack Overflow Sep 10, 2020 by Marmite Bomber

User contributions licensed under CC BY-SA 3.0