Convert YYYYMM field to date format

DHB_1
DHB_1 Member
in Workbench

 

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-_
    ST_-Superman-_ Coach
    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-_
    ST_-Superman-_ Coach
    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
  • dthierjung
    dthierjung Contributor

    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
    DHB_1 Member

    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-_
    ST_-Superman-_ Coach

    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
  • DHB_1
    DHB_1 Member

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

    Capture.PNG

  • dthierjung
    dthierjung Contributor

    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.

  • ST_-Superman-_
    ST_-Superman-_ Coach

    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
  • dthierjung
    dthierjung Contributor

    @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
    DHB_1 Member

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

This discussion has been closed.

Categories