Convert number to date inside an advanced query

Convert number to date inside an advanced query

  

Hi,

I'm trying to convert a number to date inside an advanced query. At first sight this is no big deal!

I've tried a thing like this:

SELECT
     column1,
     convert( date, column2, 112),
     (..)
FROM SomeTable
WHERE SomeCondition

When I try to test it, it says:

"Database returned the following error:
  Error in adavnced query AdvancedQuery1: SQL0206 The column or global variable DATE was not found."

Can you guys give me a hand.

I know I can use a Text data type for the output column and the use the Outsystems Built-in functions, but I was trying not to!!!

For the records, I am using the OP10, and this specific table is from a native connection to DB2.

Thanks,

Rui


Right after I've posted this, it occurred to me that the message has nothing to do with SQL or advanced query, it actually was being thrown by DB2 itself.


The correct query, when you are connecting to DB2, is:

SELECT
     column1,
     cast( cast(column2 as char(10)) as date),
     (..)
FROM SomeTable
WHERE SomeCondition

I had to first cast the decimal (it actually a decimal datatype) to char(10) and only then cast it to date, and it was Okay.

I hope this can help someone along the way!

Cheers,
Rui

Hi,

Its me again, unfortunately the above solution was not that Okay!!!! But I've finally found it!

The right answer is: 

SELECT
     column1,
     DATE(INSERT(INSERT(DIGITS(column2),5,0,'-'),8,0,'-')),
     (..)
FROM SomeTable
WHERE SomeCondition

Now its OK.

Sorry folks but this is my first time digging on DB2 (I've had too much SQL!!!).

Cheers,
Rui