question

ljbelanger avatar image
ljbelanger asked

Convert mm-dd-yyyy to read like "Jul30"

I am looking for an expression that will rename a file name to include a date format as "Jul05" at the end of the file. The expression I have now looks like this: @[User::FilePath] + "CC32012" + "-" + (DT_STR, 4, 1252) DATEPART("yy" , GETDATE()) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) +".txt" End results look like this: CC32012-2012-07-05.txt This is how I want to see the output file name: CC32012Jul05
ssisdateformatdatepart
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
ahh you might have wanted to tag this as SSIS - done now!
0 Likes 0 ·
robbin avatar image robbin commented ·
This is why I was surprised by the solutions given ;-)
0 Likes 0 ·

4 Answers

·
jhowe avatar image
jhowe answered
DATENAME("mm", GETDATE()) + DATENAME("dd", GETDATE()) ?
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
i'm not sure if there is a datename function actually in SSIS i don't think there is. You're best off going with the substring approach that kev has posted.
0 Likes 0 ·
ljbelanger avatar image
ljbelanger answered
I am not a SSIS guru like most people out there. Where or how would I use the "select" expression in your statement you posted?
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Use format style 7 (mon dd, yy) and string manipulate it.... select substring(replace(convert(varchar, getdate(), 7),' ',''),charindex(',',replace(convert(varchar, getdate(), 7),' ',''))+1,2) + substring(replace(convert(varchar, getdate(), 7),' ',''),1,charindex(',',replace(convert(varchar, getdate(), 7),' ',''))-1) gives ---------- 12Jul05
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

robbin avatar image
robbin answered
Since SSIS do not have the datename, you need to handle it with multiple boolean expressions @[User::FilePath] + "CC32012" + "-" + (DT_STR, 3, 1252)(MONTH(getdate()) == 1 ? "Jan" : MONTH(getdate()) == 2 ? "Feb" : MONTH(getdate()) == 3 ? "Mar" : MONTH(getdate()) == 4 ? "Apr" : MONTH(getdate()) == 5 ? "May" : MONTH(getdate()) == 6 ? "Jun" : MONTH(getdate()) == 7 ? "Jul" : MONTH(getdate()) == 8 ? "Aug" : MONTH(getdate()) == 9 ? "Sep" : MONTH(getdate()) == 10 ? "Oct" : MONTH(getdate()) == 11 ? "Nov" : MONTH(getdate()) == 12? "Dec":"") + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) +".txt" This can be appended in the same expression you are working in.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.