cancel
Showing results for 
Search instead for 
Did you mean: 

Converting yyyymm text to date field.

Former Member
0 Kudos
688

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)
201604201604

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:

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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
Contributor
0 Kudos

Hi Brent,

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

Thank you,

Varun Anand

Answers (1)

Answers (1)

varunanand
Contributor
0 Kudos

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
0 Kudos

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
Contributor
0 Kudos

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