InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 00:05:22 GMT20http://www.sqlservercentral.com/Forums/Topic805212-147-1.aspxThanks again, it does work now. I must have had another syntax error. I appreciate all your help with this. Have a wonderful evening. Perhaps someday I can come to your assistance.Mon, 19 Oct 2009 15:05:06 GMTRegina Beauregardhttp://www.sqlservercentral.com/Forums/Topic805212-147-1.aspxso using...DatePart("mm", GETDATE()) == 1 ? "012" : "0" + (DT_WSTR,30)(DatePart("mm", GETDATE()) - 1)doesn't work?(added the minus one at the end).Mon, 19 Oct 2009 14:59:49 GMTstevefromOZhttp://www.sqlservercentral.com/Forums/Topic805212-147-1.aspxThank you for the code and please disregard my previous reply. I get 010 when I use your example as a variable. What I need is 009 and that is where I'm having the problem. I need the current month -1 all the time. I have tried everything to get that to work.Mon, 19 Oct 2009 14:25:28 GMTRegina Beauregardhttp://www.sqlservercentral.com/Forums/Topic805212-147-1.aspxThank you again for your reply. It works and gives me 012 but I really need the expression to give me the current month -1 formatted with a preceeding 0. If I have to change it every month then there is no point to using a variable.DatePart("mm",GetDate()) -1 works fine and returns 9 but what I need it to return is 8. I need an expression that I don't have to change.Mon, 19 Oct 2009 14:17:38 GMTRegina Beauregardhttp://www.sqlservercentral.com/Forums/Topic805212-147-1.aspxSorry, looks like it's supported in 2008, perhaps not 2005. ([url=http://msdn.microsoft.com/en-us/library/ms140206.aspx][u]here[/u][/url])You can use the shortcut version of If/Iff , like .....DatePart("mm", GETDATE()) == 1 ? "012" : "0" + (DT_WSTR,30)DatePart("mm", GETDATE())Mon, 19 Oct 2009 14:09:56 GMTstevefromOZhttp://www.sqlservercentral.com/Forums/Topic805212-147-1.aspxThanks for your reply but isn't (IIf) a Microsoft Access function. I am trying to create a variable as an expression in an SSIS package. I tried what you gave me but I got an error stating IIF is unrecognized.Mon, 19 Oct 2009 13:53:21 GMTRegina Beauregardhttp://www.sqlservercentral.com/Forums/Topic805212-147-1.aspxBased on your expression for current, previous would be very similar... Note that the below will 'roll back' from a Period 01 to Period 012.IIf(DatePart("mm", GETDATE()) == 1 , "012", "0" + (DT_WSTR,30)DatePart("mm", GETDATE()))You don't mention it but make sure you account for year :-) Mon, 19 Oct 2009 13:41:10 GMTstevefromOZhttp://www.sqlservercentral.com/Forums/Topic805212-147-1.aspxI need help for creating an expression as a variable in SSIS. I need to pass a variable for the current Period -1 to a data flow task. However, I need to prefice the variable with a Zero. for instance,Period 1 = 01Period 12 = 012I can get this to work for the current period but not for period -1. This is the expression I have tried."0" + (DT_WSTR,30)DatePart("mm", GETDATE())Can someone give me a hand with this.Mon, 19 Oct 2009 11:50:30 GMTRegina Beauregard