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!
Posted by Unknown at 2:34 AM

3 comments:

  1. AnonymousJuly 1, 2011 at 9:43 AM

    thanks very much buddy.....

    ReplyDelete
  2. ashuthinksAugust 3, 2011 at 4:34 AM

    i nade it like

    @[User::Directory] + @[User::Filename]+" "+(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"

    But how can i findout yesterday's date????
    ????

    ReplyDelete
  3. AnonymousNovember 3, 2011 at 10:26 AM

    Awesome! Thanks

    ReplyDelete

Subscribe to: Post Comments (Atom)