Date Conversion

[#1]

I have a date field called "Filed_Date" in my Select from

In a Table Box Sheet Object the data looks like this:  3/26/2013 12:00:00 AM

I want to add another field in the data to select from which would look like Mar-2013.

I know in old SQL it would have been Select to_date (Filed_date, 'MMM-YYYY') as "Filed Month"

That is not working for me in QlikView.

Please help.

Thank you,

David Redden

[Gysbert_Wassenaar]

Well, to_date is an Oracle function. You can try: date(Filed_Date,'MMM-YYYY') as "Filed Month" if Filed_Date is already a date field. If it's a string try:

MonthName(date#(Filed_Date, 'M/D/YYYY hh:mm:ss TT'))

[#3]

I have tried that and when I reload the data I get the following error:

ErrorSource: Microsoft OLE DB Provider for ODBC Drivers, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

When I remove the item in bold below, it runs fine.(    date(FILED_ON,'MMM-YYYY') AS "Filed Month",)

SELECT "INCIDENT_NUMBER" as "Incident #",

    "CASE_NUMBER" as "Case #",

    "PART_SEQUENCE" as "Part Seq",

        "INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE" AS "Incident ID",

    "INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE"||'-'||"REPORT_I_F_NO" AS "Source System Ref",

    "FILED_ON" as "Filed Date",

    date(FILED_ON,'MMM-YYYY') AS "Filed Month",

      "FILED_BY" as "Filed By",

    "MFR_REPORT_NO" as "MFR Rpt #",

    "FOLLOWUP_REQUIRED" as "F/U Reqd"

    FROM CHSUSER."REGULATORY_FILINGS"

    WHERE "FILED_ON" >= SYSDATE -30

    AND "REPORT_TYPE" = 'MDR';

[Gysbert_Wassenaar · Accepted solution]

Ah yeah, feeding an oracle database qlikview functions is not going to make it happy. Qlikview sends a select statement as is to the database. It doesn't parse any of it itself. If you want to use Qlikview functions you need to do it in a load statement. You could for example you a preceding load to process the results returned by the sql statement:

load *, date([Filed Date],'MMM-YYYY') AS "Filed Month";

SELECT "INCIDENT_NUMBER" as "Incident #",

    "CASE_NUMBER" as "Case #",

    "PART_SEQUENCE" as "Part Seq",

        "INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE" AS "Incident ID",

    "INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE"||'-'||"REPORT_I_F_NO" AS "Source System Ref",

    "FILED_ON" as "Filed Date",   

--    to_char(FILED_ON,'MMM-YYYY') as Filed_Month

      "FILED_BY" as "Filed By",

    "MFR_REPORT_NO" as "MFR Rpt #",

    "FOLLOWUP_REQUIRED" as "F/U Reqd"

    FROM CHSUSER."REGULATORY_FILINGS"

    WHERE "FILED_ON" >= SYSDATE -30

    AND "REPORT_TYPE" = 'MDR';

In oracle sql you can use the to_char function to format a date as a string.

[#5]

I figured it out.

It was Load *, MonthName("Filed Date") as "Filed Month";

OLEDB CONNECT TO....

SQL SELECT....

But it was because of your response that got me there...>Thank you so much!!

Loving Qlikview!!!