sql-serverssisgetdatedatepart

SSIS Expression Builder return YY from year

I'm using SQL Server 2014\SSIS.

Using the expression builder in SSIS, how do I return the YY from the Year. I am building this into the ConnectionString Property of a Flat File Destination.

So for 2019 - return 19, 2020 - return 20 and so on.

So far I have tried the below to no avail:

(DT_WSTR, 2) DATEPART( "YY", GETDATE()) % 100

(DT_WSTR, 2) DATEPART( YY, GETDATE()) % 100

(DT_WSTR, 2) DATEPART( 'YY', GETDATE()) % 100

(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())

(DT_STR, 4, 1252) DATEPART(yy , GETDATE())

(DT_STR, 4, 1252) DATEPART('yy' , GETDATE())

DATEPART("yy",GETDATE())

DATEPART(yy,GETDATE())

DATEPART('yy',GETDATE())

(DT_STR,2,1252)DATEPART( "yy" , getdate() )

(DT_STR,2,1252)DATEPART( yy, getdate() )

(DT_STR,2,1252)DATEPART('yy', getdate() )

What am I missing?

Solution

  • Assuming you want a string, not an integer, then this'll do it:

    RIGHT((DT_WSTR, 4) DATEPART( "YY", GETDATE()),2)

    You have to be very specific with SSIS. Something like (DT_WSTR, 2) DATEPART( "YY", GETDATE()),2) will error due to a truncation error (DATEPART will return a 4 digit integer, which can't fit in a WSTR of length 2).

    Therefore you have to put the value in a string of length 4 first, and then right 2 characters.