Forum Discussion

Matas's avatar
Matas
Advocate II
4 years ago
Solved

SSIS Derived Column

Hi,

 

I have an issue and cannot find easy solution. I have a data coming as a TimeStamp in a weird format (this format cannot be changed) as "20.190.105.070.515" it indicates yyyy-mm-dd-hh-mm-ss. Using Derived Column (REPALCE(TIMESTAMP, ".", " ")) I was able to remove these dots and convert data in a database as "20190105070515". Now I would like to leave only the first 8 digits and delete the rest using Derived Column since I need to do some calculations based on the yyyy-mm-dd. Anyone could help me with a syntax or approach?


Idealy I would like to have everything in one line (changing dot to empty and substring first 8 digits) something as REPLACE(TIMESTAMP,1,8) + SUBSTRING(TIMESTAMP,1,8), but this code doesnt not work, since it replaces the dots into empty but after that it takes the whole value again from CSV file and substrings it again leaving me with even weirder result.

 

Regards,

Matas

 

 

 

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Matas's avatar
    Matas
    4 years ago

    Hi BA_Pete,

     

    Thanks for your feedback. I will remove it, can I ask how? Since it does not appear in my settings anything about deletion. 

     

    Regards,

    Matas

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Matas ,

     

    You've posted this in the Power BI Power Query forum.

    As it doesn't appear as though you want a Power Query solution, I would recommend reposting this in a more relevant forum and removing this one.

     

    Pete

    • Matas's avatar
      Matas
      Advocate II
      4 years ago

      Hi BA_Pete,

       

      Thanks for your feedback. I will remove it, can I ask how? Since it does not appear in my settings anything about deletion. 

       

      Regards,

      Matas

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Hi Matas ,

         

        No problem, I can't see that I can remove it either.

        I'll mark it as a solved solution here to avoid confusion with other users on the board.

         

        Best of luck with your question.

         

        Pete