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.

Best Answer

  • ST_-Superman-_
    Answer ✓

    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

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Answers

  • ST_-Superman-_
    Answer ✓

    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

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • 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.

  • 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?

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

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • That's what I can't find. This is the transform step.

    Capture.PNG

  • 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.

  • You beat me to it @dthierjung 


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • @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.

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

This discussion has been closed.