Converting yyyymm text to date field.

[Former Member]

I am bringing in a column in my data that is yyyymm text and creating two additional columns for yyyy and mm in order to join to other tables.

Example of Table:

YYYYMM (text)	YYYY (text)	MM (text)
201604	2016	04

I would like to create a date column based on this information. In my last step, I was going to leverage the LastDayOfMonth(<date>) calculation to align the day with the last day of each month.

I have tried three formulas:

1. MakeDate(yyyy,mm,28):

          Error Message: Function MakeDate expects parameter 1 of type 'integer', but receives 'string'. Try using the expected parameter type

2. MakeDate(ToNumber(yyyy),ToNumber(mm),28):

          Error Message: Function MakeDate expects parameter 1 of type 'integer', but receives 'double'. Try using the expected parameter type

3. ToDate(Concatenate(YYYYMM,28),"yyyy/mm/dd")

          The column populates, but it's blank.

The third try looks similar to this thread, but their answer did not work for me:

[Former Member · Accepted solution]

I was not able to find the convert to integer function (http://scn.sap.com/thread/3898922). Once this was resolved, the MakeDate function worked. Thank you Varun for the support.

[varunanand · Accepted solution]

Hi Brent,

Glad you were able to find the solution and the formula works for you !

Thank you,

Varun Anand

[varunanand]

Hi Brent,

Please try converting the columns YYYY and MM to integers using the settings and not inside the formula box.

Other approach to create a separate column by concatenating the YYYY, MM, 28 and convert it to a date field using the option above. This can later be used in the LastDayOfMonth formula as it requires a date field.

Please let me know if there are any questions or comments.

Thank you,

Varun Anand

[Former Member]

Hi Varun,

It looks like I have a new issue as I do not have an option to "convert to integer". I only have options to convert text to a number or date/time.

When a column is a number, I only have options to convert to text.

I have scanned the forums, but have not heard anyone else with this issue. Is this a setting I can turn on in order to convert something to an integer?

Brent

[varunanand]

Hi Brent,

There is no special setting to display the integer option. I am using  Lumira 1.30, please check if you have the latest version.

In spite of this limitation, I would concatenate all the text fields (YYYY,MM,28) and then covert to a date field to resolve your current issue.

Thank you,

Varun Anand