Concat with functions and quation - Data Factory

Question

I am trying to make this work but so far no luck.

WITH myTable AS ( SELECT * FROM myapp.analytics_124168.events_20220123 ) SELECT TO_JSON_STRING(t) FROM myTable as t

Instead of having 20220123, I need to generate the date so it will look like this I think;

@markus.bohland@hotmail.de (WITH myTable AS ( SELECT * FROM myapp.analytics_124168.events_@date ) SELECT TO_JSON_STRING(t) FROM myTable as t

These ` around the table name making it difficult.

Any ideas?

Thank you.

Answer 1

Hello @Bakar, Cem ,

Thanks for the question and using MS Q&A platform.

You could use the expression formatDateTime(utcnow(),'yyyyMMdd') .

It gives the date in the below format :

You can use the above expression in the concat function.

@concat('WITH myTable AS ( SELECT * FROM myapp.analytics_124168.events_',formatDateTime(utcnow(),'yyyyMMdd'),')SELECT TO_JSON_STRING(t) FROM myTable as t')  

From your comment I understand - I understand that you'd want a days old data. If that's your requirement - you could use the below expression.

@formatDateTime(adddays(utcnow(),-1),'yyyyMMdd')  

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators