How to convert string YYYMMDD to YYYY-MM (Date format)

[jmancero]

i’m working with a daily sales db that has a “Calendar day” column where the date has a yyymmdd string format. I need to get the monthly sales so I did the following:

1. Used “String manipulation” to transform “yyyymmdd” into "“yyymm01” (new column)
2. Used “String to Date&Time” to transform “yyyymm01” into date formate using “Date” as New type parameter and “yyyMMdd” as Date format.
3. Used GroupBy to accumulate sales by month using the new column.

Is there any way to go directly from “yyyymmdd” string to a (say) yyyy-mm date format?

Thanks in advance

[mwiegand]

Hey @jmancero,

did you tired the the string to date time node?

String to Date&Time

PS: If you could share some sample data, in case the above doesn’t work for you, that would be splendit!

Best
Mike

[jmancero]

Sure. Please find attached some sample data where:
CALYER = Year
Sample data_2.csv (3.1 KB)
CALDAY = YYYYMMDD (string)
Store ID
CATEGORY = Product category
SUBBRAND = Product Sub Brand
CS = Sales in units

I was trying to use “String to Date &Time” node to transform CALDAY into Year-Month without any success. That is why I ended up doing that “01” workaround.

The objetive is to group sales by Store - category and month.

[takbb]

Hi @jmancero ,

If you want to do the original conversion using a single String Manipulation node, assuming your CALDAY column is an integer (as in your example xls), you could use this:

join(substr(string($CALDAY$),0,4),"-",substr(string($CALDAY$),4,2))

Alternatively, using String to Date&Time and then back again, if your CALDAY is already a string, you can do this:

String to Date&Time using the format yyyyMMdd

followed by

Date&Time to String using the format ‘yyyy-MM’

[jmancero]

Once I have the “year and month” for every record, I will have to split the data based on that attribute (i.e.: One subset will be every sale from 2022-02 to present).

That is why I need to transform that “year and month” to date format to use the “Date & time-based Row filter” afterwards.

Both the suggestions deliver a String that I cannot transform into Date.

I have an attribute called “CALMONTH” that has year and month like this: 202202. I was thinking on transforming this string to date but no format I’ve tried seems to work (see attached).

[takbb]

Hi @jmancero,

I didn’t previously know fully what you were wanting to do. And had based my solutions on you saying said that the objective was to group sales by Store - category and month, and that you were trying to convert to yyyy-MM format so that you could use GroupBy. The above solutions would facilitate that.

Your mention of using the Date&Time based row filter, changes things. I hadn’t realised that you were actually expecting a real date.

---

OK, taking a step back then… A “date” will always consist of a year, a month and a day. You cannot have a date without a day (what specific date on the calendar would 2022-01, for example, represent?). This is why you are seeing the error message in the above config dialog. You are asking KNIME to store a date that it cannot understand, because you haven’t actually given it a date.

If you want your data in year-month format (e.g. 2012-02) , then that is always going to be a string.

Please note also that dates themselves have no format. It is quite a common misconception that dates have a format. In reality, they don’t. Internally they are just a number. Converting a string to a date does not give the date a specific format.

When displaying a date, the format used is whatever format the renderer chooses. For example, when KNIME displays a date, it may render it as 2022-06-01, but as mentioned, the internally held date itself does not have any format at all.

When you specify a date format in the String to Date&Time node, you are telling KNIME how it should interpret the string; not how the date should be formatted.

Likewise when you specify a date format in the Date&Time to String node, you are telling KNIME how to format the resultant string; not the date.

So, if you want a date in a specific format (other than KNIMES’ default rendering), it is always a string that you are formatting.

If you’re wanting to use a Date&Time Based Row Filter, then yes, I can see that you will need actual dates, but then you’ll have to go back to arbitrarily including 01 as the day, so that you can then convert your strings to real dates, like you mentioned doing before.

You will see that in the Date&Time Based Row Filter, you cannot simply choose “May 2022” to “Dec 2022”, because it wants actual dates. i.e. You always have to give it an actual date range (including the day), so I’m not sure exactly how you are intending to use that.

If you really only want to specify ranges using only year and month, then an alternative might be to make your dates into integers such as 202206 and then you can use regular Row Filter with numeric ranges e.g. 202205 to 202212. Not necessarily pretty but it would work.

[mwiegand]

Good morning and sorry for interfering @takbb

@jmancero I faced the very same issue when grouping year-month. If you want to ensure data can still be sorted properly or displayed in charts, you always kind some sort of helper columns (the original year and month). Reason is, the new chart nodes display the data in the order of the table.

Anyways, what you wanted to accomplish was quite simple … or maybe I missed something. Please have a look:

79739 - String to Date – mwiegand

Best
Mike

[takbb]

Good morning @mwiegand. You’re not interfering at all, and besides you were here first .

Yes that’s very similar to how I would have magined the required outcome to be, based on the initial post. It’s only reference to the previously unmentioned Date&time based row filter in the follow up post that has thrown me .

I realised that my last post is a little verbose and maybe even a little over the top in explanation of dates, but hopefully yours will bring everything back into focus.

[tqAkshay95]

@jmancero,

You can use the component from NodePit and select the date format you want.

For your reference, I have attached the configuration and included the download link.

Parse Date&Time — NodePit