month & year from date

[#1]

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

[sunny_talwar]

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]

Hi,

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

HTH

André Gomes

[kamal_sanguri]

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

[#5]

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

[MarcoWedel]

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;

[MarcoWedel · Accepted solution]

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

[sunny_talwar]

Good catch. Did not see that.

[#9]

True Thank you!