Using DateAdd in a DTS query designed

• Mitch2007

  SSC Eights!

  Points: 830

  More actions

  March 11, 2009 at 3:50 pm

  #201239

  Good day all,

  I am currently pulling Data from an AS400 table into SQL 2000 using A a DTS package.

  All works fine but it means I have to change the criteria every month to pull in the specific date range, in this instance the last day of the month two months back and the 17th of the last month.

  So my WHERE statement would look like this :

  WHERE "MyTable"."CYCLE_CUTOFF_DT" = '1/31/2009' OR "MyTable"."CYCLE_CUTOFF_DT" = '2/17/2009'

  By using the code below in SQL Query Analyzer I get the exact same results:

  WHERE "MyTable"."CYCLE_CUTOFF_DT" = dateadd(mm, datediff(mm, 0, getdate()) -1 , -1) OR

  "MyTable"."CYCLE_CUTOFF_DT" = dateadd(mm, datediff(mm, 0, getdate()) -1 , +16)

  If I can use this code in the DTS package it would save me having to modify each month allowing me to plug this DTS package along with a few others into one DTS package saving me further time. However the code below gives me the following error message.

  ADO error:[IBM][Client Access Express ODBC Driver(32-bit)][DB2/400 SQL]SQL0204 - GETDATE in *LIBL type *N not found.

  Can anyone please advise me on the best route to code this, if what I am trying to achieve is possible ?

  Thanks in advance,

  Mitch....

• Mohit K. Gupta

  SSChampion

  Points: 12130

  More actions

  March 11, 2009 at 4:56 pm

  #957986

  DateAdd is a SQL Server function so that why your query doesn't like it. Try looking through the ref link for Date/Time functions for DB2.

  Ref: http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

  Thanks.

  [font="Arial"]---

  Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
  Microsoft FTE - SQL Server PFE

  * Some time its the search that counts, not the finding...
  * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

  How to ask for help .. Read Best Practices here[/url].

• Mitch2007

  SSC Eights!

  Points: 830

  More actions

  March 11, 2009 at 5:10 pm

  #957991

  Ahh right, that makes perfect sense then.

  Thanks so kindly for the link, i'll look into this.

  Thanks again,

  Mitch....