Month + year concatenation mmm-yy

[infock12]

Hi all,

I have a date field and would like to get in month year format. I tried the below. I have the attendance date stored as '2016-07-12 00:00:00.000' and I would like it to show 'mmm-yy'

Month(Attendance_Date) & '-' & Year(Attendance_Date) as 'MonthYear'

but I get load error. I tried other things too by looking at other comments but i still get the same error. Any help is appreciated.

Thanks

[Anil_Babu_Samineni]

Do you have Attendance_Date fiels, If so try like below

Before moving, Try this

Load *, Date(Attendance_Date) as Sttendance_Year;

Load *, Attendance_Date FROM ......

Month(Attendance_Date,'MMM') & '-' & Year(Attendance_Date,'YY') as 'MonthYear'

OR

Month(Date(Attendance_Date),'MMM') & '-' & Year(Date(Attendance_Date),'YY') as 'MonthYear'

[tresesco]

Try like:

Date(Date#(Attendance_Date, 'YYYY-MM-DD hh:mm:ss.ffff'),'MMM-YY') As  MonthYear

[its_anandrjs]

Try this way also

Month(Attendance_Date) & '-' & Right( Year(Attendance_Date) ,2) as 'MonthYear'

Or

Date(MakeDate(Year(Attendance_Date), Month(Attendance_Date)) ,'MMM-YY') as MonthName

Regards

Anand

[gajapathy74 · Accepted solution]

Can you try this:

1. Date(MonthEnd(Floor(Attendance_Date)),'MMM-YYYY') AS MonthYear

OR

2. Date(Floor(Attendance_Date),'MMM-YYYY') AS MonthYear

(you can use MonthEnd based on your need)

[infock12]

Hi Anil,

Thanks for the reply. Yes, I do have the Attendance_Date field. I tried both the functions but it is still giving me a load error. Do I have to do anything before the original Load statement? The first two lines you mentioned above, i.e., Load*, Date (Attendance_Date) as Atendance_Year;, and the next line. I am not sure what it is. My original script starts like below and I tried the two functions below.

Load:

Attendance_Date

Field 2,

Field 3,

Month(Attendance_Date,'MMM') & '-' & Year(Attendance_Date,'YY') as 'MonthYear'

Month(Date(Attendance_Date),'MMM') & '-' & Year(Date(Attendance_Date),'YY') as 'MonthYear'

[infock12]

Thanks for the response Tresesco. This doesn't throw any error message but it gives blank results. Basically, no data in this field. When I create a list box with this dimension, it just has the heading but nothing in the list box.

[infock12]

Hi Gajapathy,

This works like a charm! Thank you very much.

[infock12]

Hi Anand,

Thank you for the quick response. This works brilliantly too! Thanks again!

[Anil_Babu_Samineni]

Karthik, Does GR Solution Works to you?

Then close this Thread by flag Correct Answer or else share your application so that we can help you