Problem with multiple date selections

[#1]

Hi All,

I have a .csv in which they unfortunately don't specify the date. It is imported as a text string which looks like: 20150127 (for 27th of January 2015). I have added the following string into the script to make sure it loads as a date:

LOAD Datum,

Date(MakeDate(Left(Datum,4),Mid(Datum,5,2),Right(Datum,2)),'YYYY-MM-DD') as Datum_New,

So far so good. I can also specify list boxes with separate Year, Month and Day functions which work. But as soon as I start making multiple selections; i.e. 2015 and March, it isn't having it. It will not select multiple selections from these boxes. How to fix this? Or a better overall solution to the problem of having these date issues in the first place?

Thank you very much!

/Maarten

[MarcoWedel · Accepted solution]

Hi,

I suppose you are selecting in calculated listboxes rather than listboxes using fields directly.

So instead of creating listboxes with expressions like month(Datum) or year(Datum) try to generate separate fields for month and year in the script.

Otherwise selections in the month(Datum) listbox reset selections in the year(Datum) listbox (because in fact they both apply selections to the same field).

One solution to do so could be:

LOAD *,

            Month(Datum) as Month,

            Year(Datum) as Year;

LOAD Date(Date#(Datum, 'YYYYMMDD'),'YYYY-MM-DD') AS Datum

           Someotherfields

From yourdatasource;

Hope this helps

Regards

Marco

[#3]

Perfect! Thank you very much. Easier solution as well

[MarcoWedel]

You're welcome.

Regards

Marco

[#5]

EDIT: The Problem has been solved by now.

Hello,

I had pretty much the same problem. I also used calculated listboxes instead of listboxes using fields directly. So I tried as explained above, but I received an error. Here is what I did:

Generic

SQL SELECT

    TIMESTAMP,

    DATA,

    WERT,

    YEAR(TIMESTAMP) as Jahr

FROM Source01;

and I got this Error:

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "YEAR": invalid identifier

Generic
SQL SELECT
    TIMESTAMP,
    DATA,
    WERT,
    YEAR(TIMESTAMPSOURCELT) as Jahr
FROM Source01;

Thinking the 'Generic' Keyword could cause the error, I tried it with a different Source:

SQL SELECT

NR as "Nummer",

    TimeStamp,

    Year(TimeStamp) as Jahr

FROM Source02;

I received the same error.

Obviously the 'Year' command does not work, but why? I'd be very grateful, if someone could help me.

Kind Regards,

phz

[Miguel_Angel_Baeyens]

Philipp,

Year() is a valid function for Oracle for date fields, but you may need to use another one like To_Date().

See Oracle documentation:

TO_DATE‌

Miguel