topic To_Date from a Timestamp field.... in QlikView

To_Date from a Timestamp field....

Tue, 07 Dec 2010 10:56:21 GMT

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?

To_Date from a Timestamp field....

Tue, 07 Dec 2010 11:05:57 GMT

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

To_Date from a Timestamp field....

hector — Tue, 07 Dec 2010 13:08:31 GMT

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

To_Date from a Timestamp field....

Fri, 10 Dec 2010 11:56:16 GMT

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.

To_Date from a Timestamp field....

Sat, 11 Dec 2010 13:40:16 GMT

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

To_Date from a Timestamp field....

Tue, 14 Dec 2010 16:26:22 GMT

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

To_Date from a Timestamp field....

Tue, 14 Dec 2010 16:30:42 GMT

You can try this

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

To_Date from a Timestamp field....

hector — Tue, 14 Dec 2010 16:41:20 GMT

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