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

Devendra Vagal said...

Thanks Phil.

Darren E said...

Thanks Phil - this did the job for me

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 ;-)

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

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

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.

Anonymous said...

Hi, How do you only get it to only do YY? i.e 110810

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.

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

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

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

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"

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

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!

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.

Ramesh Chandran 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.