cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matas
Helper I
Helper I

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

 

Screenshot_3.png

 

Screenshot_4.png

1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
BA_Pete
Super User
Super User

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

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

View solution in original post

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors