Convert YYYYMM field to date format

I have a numeric field that is setup YYYYMM that I would like to convert to a date (201812 = 2018-12-01) . This data comes through workbench 5. I can't find a way to change the field to a date in ETL so I wanted to push it in from workbench. I tried a concatenate(Left(Period,4),'-',Right(Period,2),'-',01) so it would look like 2018-12-01 but received "identifier expected". So I tried pulling out the year and month in separate transformations. Year would be Left(period,4) which was fine Validating the transformation but received "Index and length must refer to a location within the string. Parameter name: length" running the job.

My guess is the syntax doesn't follow what I'm used to in SQL. Any help with my calculation? Thanks.

Find more posts tagged with

Accepted answers

ST_Superman

You can use STR_TO_DATE() but you will first need to convert it to a string:

STR_TO_DATE( CONCAT(`Period`,'01), '%Y%m%d')

You could do this as a beastmode or as part of a select statement in a MySQL data flow

All comments

ST_Superman

You can use STR_TO_DATE() but you will first need to convert it to a string:

STR_TO_DATE( CONCAT(`Period`,'01), '%Y%m%d')

You could do this as a beastmode or as part of a select statement in a MySQL data flow

dthierjung

It's also possible that you have a row within your Period column that is less than 4 characters, which could also throw that error. Are there any nulls or values that wouldn't follow the format you specified? I ask because as far as I have experienced, the Workbench behaves syntactically the same as SQL Server Management Studio (SSMS), for example.

DHB_1

Thank you both for your answers. Yes there were zeros in my column. I am doing my first SQL dataflow to convert that column but am having difficulty with the steps. I've watched the videos which don't match what I'm seeing and the articles don't have enough detail.

I created a transform and it validates.

SELECT STR_TO_DATE(CONCAT(LEFT(Cast(`Period` AS CHARACTER),4),'-',RIGHT(Cast(`Period` AS CHARACTER),2),'-01'),'%Y-%m-%d') as Month,
sum(`Revenue`),
sum(`ReimbOtherCost`)

FROM `vision_pr_summary`
GROUP BY `Period`

I want to pull this for my Output Dataset. The instructions say to use the transform table's name but I don't see that anywhere. Help,please?

ST_Superman

The table name is in the top left. (click on the pencil icon to change it)

DHB_1

That's what I can't find. This is the transform step.

dthierjung

Oh, would you please check that when you clicked the "Add Transform" button, you have selected the "Table" and not "Transform" option?

See attached for a reference.

Capture.PNG

ST_Superman

You beat me to it @dthierjung

dthierjung

@ST_-Superman-_ I was actually on the post admiring your PhotoShop skills (Black Belt well deserved just for that detail), so I only stumbled upon the response incidentally lol.

DHB_1

Yes I did miss that detail. I have learned much. Thank you.