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)