Convert string to date format in azure data factory using set variable-CodePudding

I have string date in set variable "20211222" And I want to convert it into date like 2021-12-22. And I have use this function in variable set dynamic content

@formatDateTime('20211222', 'yyyy-MM-dd')

But error occur In function 'formatDateTime', the value provided for date time string '20211222' was not valid. The datetime string must match ISO 8601 format

Is there any other function to convert this string "20211222" into date?

CodePudding user response：

Actually the string '20211222' is already in the unambiguous format of YYYYMMDD and will always be interpreted this way. If you need to use this string input as a date, just do a cast:

SELECT CAST('20211222' AS date);  -- 2021-12-22

If you wanted to formerly go from your text input YYYYMMDD to a text output of YYYY-MM-DD, then make a round trip:

SELECT CONVERT(varchar(10), CAST('20211222' AS date), 120);

CodePudding user response：

Function formatDateTime expects "a string that contains the timestamp".

Example:

formatDateTime('03/15/2018 12:00:00', 'yyyy-MM-ddTHH:mm:ss')

You would have to manage to input in a timestamp format. The default format for the timestamp is "o" (yyyy-MM-ddTHH:mm:ss:fffffffK), which complies with ISO 8601 and preserves time zone information.

CodePudding user response：

Please use the below logic:

 @concat(substring(pipeline().parameters.Test,0,4 ),'-',substring(pipeline().parameters.Test,4,2),'-',substring(pipeline().parameters.Test,6,2))