Date formatting

[#1]

Hi

Im trying to format the following field  [0SERV_DATE].[20130401] into 'YYYY-MM'.

I managed to format it into a date using:

     date(makedate(left(left(right([Serv. Rendered],9),8),4),

           mid(left(right([Serv. Rendered],9),8),5,2),

           right(left(right([Serv. Rendered],9),8),2)),'YYYY-MM-DD') as ServiceRenderedDate

but when i try formating this into 'YYYY-MM' using ...

date(ServiceRenderedDate,'YYYY-MMM') as ServiceRenderedMonth

i get for example ...

2013-Apr

2013-Apr

2013-Apr

2013-Apr

2013-Apr

2013-Apr

2013-Apr

Any suggstions will be appreciated.

Regards,

Magen

[anbu1984]

Date(Date#('20130401','YYYYMMDD'),'YYYY-MMM') --2013-Apr

Date(Date#('20130401','YYYYMMDD'),'YYYY-MM') --2013-04

[MarcoWedel]

date(ServiceRenderedDate,'YYYY-MM') as ServiceRenderedMonth

[MarcoWedel]

better

date(MonthStart(ServiceRenderedDate),'YYYY-MM') as ServiceRenderedMonth

to get only one value for all dates in this month.

[#5]

Hi Anbu

Im not quite sure about your solution ..

You placed the actual string into the date function and not the field name, even if i place in the field name like so ..

Date(Date#(ServiceRenderedDate,'YYYYMMDD'),'YYYY-MMM') as ServiceRenderedMonth

it returns no values...

Regards,

Magen

[#6]

Hi Marco

Thanks for your reply but the solution returns null values ..

Regards,

Magen

[anbu1984]

What is the format of ServiceRenderedDate?

[#8 · Accepted solution]

Hi guys

No problem guys i got the solution ...

So the original field is [0SERV_DATE].[20130401]

so i scripted ..

date(date#(left(right([Serv. Rendered On - Serv. Rendered On Level 01 (Key)],9),6),'YYYYMM'),'YYYY-MM') as ServiceRenderedMonth,

and received  ...

2013-04

2013-05

2013-06

2013-07

2013-08

2013-09

2013-10

2013-11

2013-12

2014-01

2014-02

2014-03

2014-04

2014-05

2014-06

2014-07

2014-08

2014-09

2014-10

Worked like a charm 🙂

Regards,

Magen

[anbu1984]

=Date(Date#(TextBetween('[0SERV_DATE].[20130401]','[',']',2),'YYYYMMDD'),'YYYY-MM')