SSIS - specify the date to use in the expression

Question

I want to specify a date in ISO format and use it as a string in an expression in SSIS.

This is what I type in T-SQL

select convert(varchar(8), GetDate(), 112)

and this is what I will return

20100630

My goal is to create an archive folder based on the date. I have a File System task to create a folder sorted by, I can do it ... I just need to be able to pass the date to a string, so I can use it.

Thanks in advance.

Answer 1

You need to add an expression:

RIGHT((DT_WSTR, 4) DATEPART("yyyy", GetDate()), 4) + 
RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + 
RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2)

This expression will create the result that you are after.

Answer 2

Old question, best answer, from Adrian's comment here :

, DT_DBDATE. WSTR "--", :

REPLACE ((DT_WSTR, 200) (DT_DBDATE) GETUTCDATE(), "-", "")

REPLACE, YY-MM-DD

Answer 3

In SSIS, you must use the DT_STR or DT_WSTR commands to execute. A few examples:

(DT_STR, 4, 1252)YEAR(GETDATE()) + 
RIGHT("0" + (DT_STR, 2, 1252)MONTH(GETDATE()), 2) + 
RIGHT("0" + (DT_STR, 2, 1252)DAY(GETDATE()), 2)

or

(DT_WSTR, 4)YEAR(GETDATE()) + 
RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2) + 
RIGHT("0" + (DT_WSTR, 2)DAY(GETDATE()), 2)

See the MSDN documentation for more information.

Answer 4

To add to the other answers, a reference guide is often used here:

http://sqlis.com/sqlis/post/Expression-Date-Functions.aspx