1

I have an Oracle database and I have to load dat from this database to Azure SQL DWH. This is done once every day. At the beginning of the pipeline I first do a lookup on SQL DWH to look for the latest date. The result for that is something like '2015-10-25'. I want to use this date to query the Oracle database. But I allready found out, by trying the query on Oracle that the following code does not work:

Select * from Table 1 where day = '2015-10-25'

The date in the day column looks like 25-OCT-15 (DD-MON-YY).

I treid the following where clause:

where day = TO_DATE('2015-10-25','DD-MON-YY')

But then I get the error: "literal does not match format string"

I realy don't know how to make Oracle understand this T-SQL date format.

jbazelmans
  • 283
  • 1
  • 6
  • 16
  • Don't you want `TO_DATE('2015-10-25','YYYY-MM-DD')`? Assuming the target column is actually a `date` datatype. – wBob Sep 25 '19 at 14:15
  • Is this a question about how to format dates in Data Factory (v2)? Or is this more about what is accepted by Oracle? – MartinJaffer-MSFT Sep 25 '19 at 19:31
  • 1
    Thanks @wBob. That did the trick. I was a bit confused. But now I understand that in Oracle I have to say the string on the right side is a dat and tell Oracle what the format is of that date. Normally, in T-SQL I would convert the string on right side to the format that is in the table – jbazelmans Sep 26 '19 at 06:08

2 Answers2

0

You can try this query:

Select * from Table 1 where day = to_char (to_date('2015-10-25','YYYY-MM-DD'), 'DD-Mon-YY')

Reference this blog: how to convert YYYYMMDD to DD-Mon-YYYY in oracle?

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
0

Your Oracle column is of date datatype. When you connect to an Oracle database and write a query against that date column, you will see its default format DD-MON-YY as per this reference.

You can override this setting by running an ALTER SESSION command, eg

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';

but this is just in that local session. The data is still stored in Oracle in the same manner and it's simply the way you view it that is changing.

In Azure Data Factory (ADF v2) and your example, you are dealing with strings. So you must make sure any parameters you pass in are in the correct format or set to the correct datatype. The Oracle function TO_DATE converts strings to the date datatype. Therefore when passing in a string of format YYYY-MM-DD then that is the format you must use, to let the TO_DATE function know what you are passing in:

TO_DATE('2015-10-25','YYYY-MM-DD')

The function then successfully converts your parameter to a date datetype for correct comparison against the main date column.

wBob
  • 13,710
  • 3
  • 20
  • 37