Duplicate Dates in Date Field - Floor not working

[MalcolmCICWF]

I have a Date I am pulling in from a qvd formatted YYYYMMDD. I have tried everything to reformat and get the date to be just YYYYMM format. No matter the formatting I try, the closest I can get is the correct format, but then duplicates of dates like the Day is still attached. I tried Floor to remove it and group them again, but to no avail.

This is the script currently getting me the closest, but still duplicates.

Date(Floor(DATE#(DATEFIELD,'YYYYMMDD')),'YYYYMM') AS NEWDATE,

[jwjackso · Accepted solution]

The duplicate dates (2016-04) probably come from having records from 3 different days in April.  You could "select distinct NEWDATE from resident "table name" to eliminate the duplicates.

But if you are not using a calendar in your app, I would suggest that a calendar will make your life easier.

[Vegar]

It's just as previous speaker mentioned, you get multiple days with the same text formatting.

You could try this:
Date(monthname(DATE#(DATEFIELD,'YYYYMMDD')),'YYYYMM') AS NEWDATE,

Or even simpler:
monthname(DATE#(DATEFIELD,'YYYYMMDD')) AS NEWDATE,

[florentina_doga]

use distinct