CODEWITHSUNDEEP
ssisiso8601
vani malge
vani malge

Reputation: 85

Datetime to convert to ISO8601 format

I have a getdate() field and I want to convert it into 20210211T172650Z this format how do I do it in SSIS expression?

Upvotes: 1

Views: 1322

Answers (1)

billinkc
billinkc

Reputation: 61249

In SSIS, we have data types for strings, numbers and dates. Dates have no format and when it is converted to a string value, you're getting whatever format the localization rules dictate.

If you have a particular format you want, then you need to control that and the only way you can control it, is by using a string data type.

The pattern we're going to use here, for each element,

  1. extract the digit(s)
  2. convert the digits to string
  3. left pad/prepend a leading zero
  4. extract the last 2 characters from our string

When we extract digits, they're numbers and numbers don't have leading zeroes. We convert to string which will allow us to then add the character zero in front of it because we're just concatenating strings. If the number was less than 10, then this prepending of a zero will result in exactly what we want. 9 -> 09 If it was greater than 9, then we have an extraneous value in there. 11 -> 011. We don't care that we went too big because we're then going to take the right 2 most characters making 09 -> 09 and 011 -> 11. This is the shortest logic to making a leading zero string in SSIS.

Using that logic, we're going to create a variable for each element of our formatted string: year, month, day, hour, minute, second.

What's the starting date?

I created a variable called StartDate of type DateTime and hard coded it to a starting point. This is going to allow me to test various conditions. If I used getdate, then I'd either have to adjust my computer's clock to ensure my code works on 2001-01-01 at 01:01:01 as well as 2021-12-31 at 23:59:59. When you're satisfied your code passes all the tests, you can then specify that StartDate property EvaluateAsExpression is True and then use GetDate(). But I wouldn't use GetDate().

GetDate is going to evaluate every time you inspect it. When your package starts, it will show 2021-02-12 @ 11:16 AM But your package takes 5 minutes to run, so when you go to re-use the value that is built on GetDate, you will now get 2021-02-12 @ 11:21 AM.

In your case, those keys won't match if you send it more than once to your Amazon thing. Instead, use a System scoped variable like @[System::StartTime] That is updated to the time the package starts executing and remains constant for the duration of the SSIS package execution. So when you're satisfied the expression you've build matches the business rules, then change @[User::StartDate] over to use @[System::StartTime]. It provides the updated time but without the challenges of drifting time.

Extract the digit(s)

The SSIS expression language has YEAR, MONTH and DAY defined but no shorthand methods for time components. But, it does have the DATEPART function in which you can ask for any named date part. I'm going to use that for all of my access methods as it makes it nice and consistent.

As an example, this is how I get the Hour. String literal HOUR and we use our variable

DATEPART("HOUR",@[User::StartDate])

Convert the digits to string

The previous step gave us a number but we've got that leading zero problem to solve so convert that to a string

(DT_WSTR, 2)DATEPART("HOUR",@[User::StartDate])

Cast to string, two characters wide max, the number we generated

left pad/prepend a leading zero

String concatenation is the + operator and since we can't concatenate a string to a number, we make sure we have the correct operand types on both sides

"0" + (DT_WSTR, 2)DATEPART("HOUR",@[User::StartDate])

extract the last 2 characters from our string

Since we might have a 2 or 3 character string at this point, we're going to use the RIGHT function to only get the last N characters.

RIGHT("0" + (DT_WSTR, 2)DATEPART("HOUR",@[User::StartDate]), 2)

Final concatenation

Now that we have our happy little variables and we've checked our boundary conditions, the only thing left is to make one last variable, DateAsISO8601 type of string, EvaulateAsExpression = True

@[User::Year] + @[User::Month] +@[User::Day] + "T" +@[User::Hour] +@[User::Minute] +@[User::Second] + "Z"

Upvotes: 1

Related Questions