Sunday, August 23, 2009

Getting yesterday date properly formated using SSIS expressions

Well In case you want today in the following format:
2009-08-23

Notice the trailing zero before the 8. Heres how:
(DT_WSTR, 4) DATEPART( "yyyy", DATEADD( "dd" , -@DateCounter, GETDATE() ) )
+ "-" +
Right("0"+(DT_WSTR, 2)DATEPART( "mm", DATEADD( "dd" , -@DateCounter, GETDATE() ) ),2)
+"-"+
Right("0"+(DT_WSTR, 2)DATEPART( "dd", DATEADD( "dd" , -@DateCounter, GETDATE() ) ) ,2)
+" 00:00:00"

the hours mins and seconds are non dynamic in this case but I hope you get the drift.

The variable DateCounter will take you back as many days as you like.

A little more one how adding the zero worked:
  • DT_WSTR,2 made the "mm" section return X8 where X is null.
  • "0" + "X8" = "08" . But had it been say "11" then "0"+"11" = "011".
  • Right (,2) made "08" = "08 but had it been "011" as explained above it would be "11"
Enjoy!