CODEWITHSUNDEEP
sql-server-2005ssis
cometbill
cometbill

Reputation: 1619

SSIS - cast a date to use in an expression

I want to cast a date into 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 get back

20100630

My goal - is to create an archive folder based on the date. I have the File System Task part of creating a folder sorted, I can do that ... I just need to be able to Cast the date to a string, so I can use that.

Thanks in advance.

Upvotes: 3

Views: 16529

Answers (4)

Jeff
Jeff

Reputation: 12785

Old question, better-er answer, from Adrian's comment here:

Try an intermediate cast to type DT_DBDATE. Casting to WSTR gives a string in format "YYYY-MM-DD", which is easily tidied up:

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

Or use without the REPLACE to get the YY-MM-DD

Upvotes: 3

etliens
etliens

Reputation: 1342

To add to the other answers, here is a reference guide I use frequently:

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

Upvotes: 2

Neil Knight
Neil Knight

Reputation: 48547

You'll 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.

Upvotes: 2

Garett
Garett

Reputation: 16828

In SSIS you would use the DT_STR or DT_WSTR cast to accomplish. A few examples are

(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.

Upvotes: 2

Related Questions