none
SSIS Dynamic Date Query

    Question

  • How to change this in an SSIS OLE DB Source SQL command text. (SQL Server 2008 R2) 
    to make it select the records from the past 12 months dynamically. (Rotating) 

    and trunc(open_time) > to_date('05/31/2012','MM/DD/YYYY')
    Thursday, June 20, 2013 2:36 PM

All replies

  • Assuming you do not need anything more complex you can use the following in your WHERE clause

    DATEADD(Month, -12, GETDATE())

    Kind regards,

    M

    Thursday, June 20, 2013 2:41 PM
  • Create a string variable varDate and set its expression as :

    RIGHT("0"+ (DT_WSTR,30)(DATEPART("MM",DATEADD("MM",-12,GETDATE()))),2)+"/"+
    RIGHT("0"+ (DT_WSTR,30)(DATEPART("DD",DATEADD("MM",-12,GETDATE()))),2)+"/"+
    (DT_WSTR,30)(DATEPART("YY",DATEADD("MM",-12,GETDATE())))

    Use this variable in to_date function as parameter Or create a variable and write an expression for the query as: "Select from table where opentime > to_date('"+@[User::varDate]+"', 'MM/DD/YYYY'".

    Use this new variable as query source


    Nitesh Rai- Please mark the post as answered if it answers your question


    • Edited by Nitesh Rai Thursday, June 20, 2013 2:52 PM Added code
    Thursday, June 20, 2013 2:49 PM
  • where ASSIGNMENT IN ('crosby', 'rs-cro-nh')
    and trunc(open_time) > to_date('05/31/2012','MM/DD/YYYY')

    This is P/SQL - i.e. Oracle. Isn't it?
    Thursday, June 20, 2013 2:55 PM
  • DATEADD cannot be used
    Thursday, June 20, 2013 2:56 PM
  • Yes it is
    Thursday, June 20, 2013 2:57 PM
  • DATEADD cannot be used

    Why?

    Nitesh Rai- Please mark the post as answered if it answers your question

    Thursday, June 20, 2013 2:58 PM
  • My P/SQL is a little rusty but ADD_MONTHS(GETDATE(), -12) in your work might work.

    Kind regards,

    M

    Thursday, June 20, 2013 3:00 PM
  • It says it is an invalid identifier
    Thursday, June 20, 2013 3:00 PM
  • It says it is an invalid identifier

    You mean invalid identifier for DateAdd??


    Nitesh Rai- Please mark the post as answered if it answers your question

    Thursday, June 20, 2013 3:03 PM
  • Does this SELECT add_months(SYSDATE, -12) FROM DUAL work?
    Thursday, June 20, 2013 3:04 PM
  • Are you trying to get the equivalant TSQL command or Oracle command?

    Thursday, June 20, 2013 3:13 PM