Search code examples
sql-serverssissqldatatypesdts

How to do format expression in ssis expression language

In sql we have got data type format like

CONVERT(VARCHAR(5), CONVERT(DATETIME, [ReferralDate], 0), 108)

Is there any way to achieve the same using ssis expression language?

yes we can convert to string or any other required data types using the expression similar to

(DT_STR, 10,1252)dob

But my question is how to get the required format by passing the number values such as 108(as mentioned in the above sql) or 123/106 etc

Solution

  • There is no analog in the Expression language for locale formatting. You'd need to use a Script Task or Component to apply formatting. Custom Date and Time Format Strings

    That said, your first example seems to be extracting the hours and minutes from a date in hh:mm format while I assume the other is going to be converting a date in a string using whatever your locale specifies.

    STOP STORING DATES AND TIMES IN STRING DATA TYPES!

    The data types of date, datetime, time, etc store the values in a local agnostic mechanism. That means you don't have to worry about whether a string value of "01-02-05" is "Feb 1 2005" -> en-GB, "Jan 2 2005" -> en-US or "Feb 5 2001" -> ja-JP. Instead, these are presentation concepts that have no business being in your database.