To_Date from a Timestamp field....

[#1]

I've tried to use that query but it seems that it doesn't accept the TIMESTAMP format of date field:

LOAD HOST, DATE_C;
SQL SELECT HOST, TO_DATE(DATE_C,'AAAA-MM-DD hh:mm:ss.fff')
from TABLE1;

Ans this is the error: SQL##f - SqlState: S1000, ErrorCode: 1821, ErrorMsg: [Oracle][ODBC][Ora]ORA-01821: date format not recognized

ANy ideas?

[#2]

Hello Qlikview-tester,

if you can't get rid of the sql-error try an "select field_list from..." and do the formatting within the load statement of QV. So you do not need to struggle with sql and you are testing a bit more of QV.

HtH

Roland

[hector]

Hi, i'm not an expert in oracle, but AAAA means the year with 4 digits?? isn't the standard format for this value YYYY??

Also, in qlikview you can do the same function with timestamp(FIELD, ,'YYYY-MM-DD hh:mm:ss.fff'')

Rgds

[#4]

Hi Roland,

doing the query without formating doesn't work.

Qlikview gets all the previous fields before the TIMESTAMP one. At that point, it finishes the query witout any extra info....

That's the reason I tried to get it and reformat at the same time.

[#5]

OK, then you have to find the correct format. I assume you found it meanwhile. If not, I think Hector is right: a 'AAAA' does not exist as a valid date format in oracle. Use 'YYYY' instead. For Details take a glance at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

RR

[#6]

I get that error message:

SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

SQL SELECT SSERVER, timestamp(DTDATE, ,'YYYY-MM-DD hh:mm:ss.fff')
from TABLE1

[#7]

You can try this

SQL SELECT SSERVER, to_char(DTDATE, ,'YYYY-MM-DD hh:mm:ss.fff')
from TABLE1

[hector]

Hi again, can you try this code??

QV_TABLE:
LOAD HOST, DATE_C_DATEFORMAT;
SQL SELECT HOST, TO_DATE(DATE_C,'YYYY-MM-DD') as DATE_C_DATEFORMAT
from TABLE1;
OR

QV_TABLE2:
LOAD HOST, DATE_C_DATEFORMAT;
SQL SELECT HOST, TO_DATE(DATE_C) as DATE_C_DATEFORMAT
from TABLE1;
OR

LOAD HOST, date(DATE_C,'YYYY-MM-DD');
SQL SELECT HOST, DATE_C
from TABLE1;

Rgds