Phil's fairly useful blog: SSIS Expression for YYYYMMDD (ISO date)

Friday, 25 January 2008

SSIS Expression for YYYYMMDD (ISO date)

Scratching around this afternoon trying to find a crib for the SSIS expression for the date format YYYYMMDD (ie 20080125 for today). Darren's Wiki site has it here but it appeared to be having a few problems and the page was timing out (seems to working now). So I'm sticking it here should I need it again (and I probably will):

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

Update: Thanks to Anonymous below who pointed out that the code-page ought to be 1252 (rather than 1253) since this is usually the default for most SQL installs (not that 1253 will break it). As you can see I've updated it. Also if you want a Unicode version you can do this:

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

17 comments:

Anonymous said...: excellent!! added the RIGHT function to my expression and is working now! thanks!

Lily,
South Africa; 5 May 2009 at 09:13
Devendra Vagal said...: Thanks Phil.; 19 October 2009 at 22:06
Unknown said...: Thanks Phil - this did the job for me; 22 April 2010 at 12:59
Anonymous said...: 1253 is the codepage "Windows Greek". perhaps that was intended, but most people want the "Latin I" code page 1252. (Not that it really matters here, since numbers are in the same position)

But if there is no reason why you cannot use Unicode, you should really change that to:

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

which is also shorter ;-); 26 April 2010 at 12:17
Anonymous said...: 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(),"-","")

Cheers,
Adrian,
London; 12 May 2010 at 16:58
VASANT JAGTAP said...: hi,
Can I use tbale date data column using MIN or MAX in Expression?

ex.
select MAX(tradedate) from dbo.tbTradeDate

DT_WSTR,4)YEAR(GETDATE())

I want to replace GETDATE as MAX(tradedate) from table.
please help me.

Thanks
Vasant; 6 August 2010 at 15:10
Phil said...: Hi VASANT,

MIN or MAX aren't supported in SSIS Expressions, in fact no aggregate functions are - thats not what expressions are for. You've a number of options to get the MAX(tradedate) - basically it boils down to executing a SQL query in a data source transform or using an Aggregate transform with the former probably being preferable.; 6 August 2010 at 19:49
Anonymous said...: Hi, How do you only get it to only do YY? i.e 110810; 10 August 2011 at 12:21
Phil said...: Hi Anonymous,

You just need to use the RIGHT function on the YEAR part of the expression ie: RIGHT((DT_STR,4,1252)YEAR(GETDATE()),2) + RIGHT("0" + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT("0" + (DT_STR,2,1252)DAY(GETDATE()),2)

Hope this helps.

Phil.; 12 August 2011 at 22:29
Anonymous said...: Hello,

Excellent post. This had be baffled for a long time.
I wondered if you could show how to use a file with yesterdays date in the filename, rather than the current day.

I have tried Getdate()-1) on the 'DATE' section of the expressions, but inevitably get errors when parsing.

Thanks in advance, your blog is supurb

Snork; 29 September 2011 at 15:42
Phil said...: Snork,

You need to use DATEADD to perform date manipulation which happily, works in a similar fashion to the T-SQL version. In your case you'd need to use DATEADD("dd", -1, GETDATE()); 29 September 2011 at 21:02
Snork said...: Hi Phil,

Thanks for the very prompt reply.

Sorry for being thick, I'm a bit of a novice at this SSIS business and wondered where exactly I insert the DATEADD("dd", -1, GETDATE()) in the expression.

Here is my current expression which return the file from the current day.

"Customers_"+(DT_STR,4,1252)YEAR(GETDATE()) + RIGHT("0" + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT("0" + (DT_STR,2,1252)DAY(GETDATE()),2)+"_235900"+".csv"

I really appreciate any help you provide.

Snork; 30 September 2011 at 09:34
Phil said...: Snork,

You would wrap each instance of the GETDATE() function with DATEADD. Hence: (DT_STR,4,1252)YEAR(DATEADD("dd", -1, GETDATE())) + RIGHT("0" + (DT_STR,2,1252)MONTH(DATEADD("dd", -1, GETDATE())),2) + RIGHT("0" + (DT_STR,2,1252)DAY(DATEADD("dd", -1, GETDATE())),2)+"_235900"+".csv"; 8 October 2011 at 17:23
Snork said...: Hi Phil,

That worked perfectly. Thanks very much for the help, you've saved me hours of admin.

You're a gent and a scholar.

Best wishes,

Snork; 13 October 2011 at 15:26
Anonymous said...: BIDS Helper includes this as an expression in the Expression Library. It also includes other common date and file expressions. Check it out!; 24 February 2012 at 21:28
Anonymous said...: Using:
SUBSTRING(REPLACE(REPLACE((DT_WSTR,200)GETDATE(),":","")," ","-"),1,17)

Returns date in format:
2012-10-16-160144

I use this to append to a filename a date stamp that contains a unique date with year to seconds.; 16 October 2012 at 21:05
Unknown said...: Hi, I was wondering if some one could help me out here.

I just wanted to get a simple dynamic date and used the expression below....

"C:\\Users\\Savi\\Desktop\\SQL BI\\Test BI\\EMP_FF_"+
(DT_STR,4,1252)YEAR(GETDATE()) + RIGHT("0" + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT("0" + (DT_STR,2,1252)DAY(GETDATE()),2)+
".txt"

The evaluate expression shows me the file location as

C:\Users\Savi\Desktop\SQL BI\Test BI\EMP_FF_20121129.txt

but when i go to the folder it ends up just as

C:\Users\Savi\Desktop\SQL BI\Test BI\EMP_FF.txt.

Could someone look at my expression above and tell me what I'm doing wrong here.

Thanks for your time. Great work by the way.; 30 November 2012 at 01:51