creating a period 'yyyy-mm' from a date field

[Anonymous]

I would like to have a field which shows the year then month of the transaction as a field selected in my reports. I've highlighted my attempt to create the field. Can someone please advise me what I have done wrong?

LOAD

  `st_account` as %Key.Customer,

  `st_account`,

    `st_trdate`,

     date(date#(`st_trdate`, 'dd-mm-yyyy'),'yyyy-mm') as Period,

    `st_trref` AS %Key.Trans,

    `st_custref`,

    `st_trtype`,

    `st_trvalue`,

    `st_vatval`,

    `st_trbal`,

    `st_paid`,

    `st_crdate`,

    `st_advance`,

    `st_payflag`,

    `st_dueday`,

    `st_type`,

    `st_delacc`,

    id,

    `st_pedate`;

SQL SELECT `st_account`,

    `st_trdate`,

    `st_trref`,

    `st_custref`,

    `st_trtype`,

    `st_trvalue`,

    `st_vatval`,

    `st_trbal`,

    `st_paid`,

    `st_crdate`,

    `st_advance`,

    `st_payflag`,

    `st_dueday`,

    `st_type`,

    `st_delacc`,

    id,

    `st_pedate`

FROM stran;

[vishsaggi]

What is the format for `st_trdate' field coming from your Database?

[#3]

guess

Date(st_trdate,'YYYY-MM') as Period

should be fine.

What is the error you are getting?

Thank you!

[Anil_Babu_Samineni]

May be using Preceding Load. Try with these 3 conditions

Load *, date(date#(`st_trdate`, 'dd-mm-yyyy'),'yyyy-mm') as Period;

//Load *, date(date#([`st_trdate`], 'dd-mm-yyyy'),'yyyy-mm') as Period;

//Load *, date(date#(st_trdate, 'dd-mm-yyyy'),'yyyy-mm') as Period;

LOAD

  `st_account` as %Key.Customer,

  `st_account`,

    `st_trdate`,

    `st_trref` AS %Key.Trans,

    `st_custref`,

    `st_trtype`,

    `st_trvalue`,

    `st_vatval`,

    `st_trbal`,

    `st_paid`,

    `st_crdate`,

    `st_advance`,

    `st_payflag`,

    `st_dueday`,

    `st_type`,

    `st_delacc`,

    id,

    `st_pedate`;

SQL SELECT `st_account`,

    `st_trdate`,

    `st_trref`,

    `st_custref`,

    `st_trtype`,

    `st_trvalue`,

    `st_vatval`,

    `st_trbal`,

    `st_paid`,

    `st_crdate`,

    `st_advance`,

    `st_payflag`,

    `st_dueday`,

    `st_type`,

    `st_delacc`,

    id,

    `st_pedate`

FROM stran;

[sasiparupudi1]

Try

date(Floor(st_trdate),'yyyy-mm') as Period

hth

Sas

[jonathandienst]

Your problem is that you will get mutiple values of each period, with the underlying value being the actual date (ie up to 31 distinct values). The Date() function does not truncate the day when you format with something like 'yyyy-mm', it just does not display it.

You need

    Date(MonthStart(Date#([st_trdate], 'dd-mm-yyyy'),'yyyy-mm')) as Period,

    Date(MonthStart(Date#([st_trdate], 'dd-mm-yyyy')), 'yyyy-mm') as Period,

This ensures that there is only one possible value for each Period.

(edit - bracket placement corrected)

[PrashantSangle]

Hi,

What kind of issue you are facing???

in date format

mm - > minutes

MM -> Month

Check date format also.

Regards,

Prashant

[muthukumar77 · Accepted solution]

May be like this,

Date(Floor(st_trdate),'YYYY-MM')

[hemachandran]

try this Expression,

=Date(floor(st_trdate),'YYYY-MM')

[Anonymous]

Thank you for your help this has resolved the problem.