How do I format date value as yyyy-mm-dd using SSIS expression builder?

Question

hi I have taking flatfile source name dynamically I.e. filename like "source 2011-08-11" I'm creating expression builder for taking most recent file as per filename. I did like Created one variable which is having folder path : C\backup\

now inside expression builder how can i add date ??? i tried like

@[User::DirPath]+"source"+ (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))
+"-"+(DT_STR,4,1252)MONTH( DATEADD( "dd",-1, getdate() ))+"-"+(DT_STR,4,1252) 
DAY(DATEADD( "dd", -1, getdate() )) +".CSV"

which is wrong please give me the expression which gives me output : "source 2011-08-11"

score 54 · Accepted Answer · edited Mar 15 '19 at 07:36

54

Correct expression is

"source " + (DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) + "-" +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) + "-" +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) +".CSV"

edited Mar 15 '19 at 07:36

llessurt

555
3
14

answered Aug 03 '11 at 06:50

Deepankar Sarkar

1,565
1
13
19

hey if i need to change date then where i need to change the value – Neo Aug 03 '11 at 08:37
6

+1, though I wish there was another way. That degree of complexity for something almost every package needs is just obscene. SSIS is worth every penny of its price. – Sep 03 '15 at 20:08
1

That evals to 20110811 not 2011-08-11. You need a "-" + after the first 2 +. – SteveCav Nov 08 '17 at 00:23
2

Thanks. It was taking forever on how to do this - I thought there must be a simpler way but evidently not. 6 functions to format a date - pretty lame. FU MS. – Hannover Fist Jun 14 '18 at 23:51
Just remember - if it gets too ridiculous you can always use a script task with C#. However I would say the first rule of SSIS is "Try not to use a script task". – dyslexicanaboko Feb 11 '20 at 21:39

score 3 · Answer 2 · edited May 23 '17 at 10:30

Looks like you created a separate question. I was answering your other question How to change flat file source using foreach loop container in an SSIS package? with the same answer. Anyway, here it is again.

Create two string data type variables namely DirPath and FilePath. Set the value C:\backup\ to the variable DirPath. Do not set any value to the variable FilePath.

Variables

Select the variable FilePath and select F4 to view the properties. Set the EvaluateAsExpression property to True and set the Expression property as @[User::DirPath] + "Source" + (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)

Expression

score 2 · Answer 3 · edited Jul 10 '22 at 05:13

2

"source " + LEFT((DT_WSTR, 50)(DT_DBTIMESTAMP)GETDATE(), 10) +".CSV"

enter image description here

edited Jul 10 '22 at 05:13

Adrian Mole

49,934
160
51
83

answered Jul 04 '22 at 16:13

GRIGORIY LVOV

21
2

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 08 '22 at 00:24
this was the most simple and useful for standard format – Abel Matos Nov 23 '22 at 16:53

How do I format date value as yyyy-mm-dd using SSIS expression builder?

3 Answers3

Linked