Month & '/1/' & Year from Date

[carlcimino]

Hello, I am trying without success to create a new date field from an existing date but only use the month and year and have the day be = 1.

This is my field NB_EFFECTIVE_DATE in the format 'mm/dd/yyyy'; the field i want would be month(NB_EFFECTIVE_DATE) & '/1/' & year(NB_EFFECTIVE_DATE).

I tried this:  date(num(month((date([NB Effective Date])))&'/1/'&year(date([NB Effective Date])))) but it didn't work.

any suggestions?

Thanks!

[sunny_talwar · Accepted solution]

Try this

Date(MonthStart(NB_EFFECTIVE_DATE)) as NewField

[carlcimino]

worked thank you.  So many date functions to sift through!