Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

month & year from date

Hi Experts

Hoe can I script to receive a month and a year out of a field containing a date? I tried the following which did not work:

SQL SELECT jobnr,

    datum,

    Month (datum)& Year (datum) as StundenMonat,

    stunden,

    stundenart,

    marnr as MitarbeiterStunden

FROM EASY.stunden;

I would like to have: 01 2014, 02 2014 e.g.

Thank you for your help!

Jan

Tags (1)
1 Solution

Accepted Solutions

Re: month & year from date

or better

LOAD *,

        Date(MonthStart(datum),'MM YYYY') as StundenMonat;

SQL SELECT jobnr,

    datum,

    stunden,

    stundenart,

    marnr as MitarbeiterStunden

FROM EASY.stunden;

because this will create a real date instead of text, i.e. no sorting and calculation issues ...

regards

MArco

8 Replies

Re: month & year from date

I wonder why it did not work. But Try this instead.

Month(Date(datum))& ' ' & Year(Date(datum)) as StundenMonat


This may or may not work, but worth giving a shot.


Best,

S

agomes1971
Valued Contributor

Re: month & year from date

Hi,

maybe  Month (datum)&' '& Year (datum) as StundenMonat

HTH

André Gomes

kamal_sanguri
Valued Contributor

Re: month & year from date

Hi,

You can use month and year function to take out the respective part from Date.

For Ex: if you want to pull number of the month you can try

numsum(month(DateField)) & Year (DateField)

Thanks,

kamal

Not applicable

Re: month & year from date

Check whether datum is in date format . If yes then try onth (datum)&' '& Year (datum)

Re: month & year from date

you're mixing QV script with SQL.

Try

LOAD *,

         Month (datum)& Year (datum) as StundenMonat;

SQL SELECT jobnr,

    datum,

    stunden,

    stundenart,

    marnr as MitarbeiterStunden

FROM EASY.stunden;

Re: month & year from date

or better

LOAD *,

        Date(MonthStart(datum),'MM YYYY') as StundenMonat;

SQL SELECT jobnr,

    datum,

    stunden,

    stundenart,

    marnr as MitarbeiterStunden

FROM EASY.stunden;

because this will create a real date instead of text, i.e. no sorting and calculation issues ...

regards

MArco

Re: month & year from date

Good catch. Did not see that.

Not applicable

Re: month & year from date

True Thank you!

Community Browser