Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Posts
    26

    Unanswered: Date variables/Functions for DB2 in SQL DTS

    Good day everyone.
    I have a SQL DTS package that runs the following code and pulls the Data from an AS400 table to a SQL table. This all works rather well but i'd rather use a date function to determine the Data rather then havign to change the hard coded part on a monthly basis. The criteria looks like this:-

    WHERE
    (AS400Table.AS400Field BETWEEN 20100301 AND 20100331)
    or
    (AS400Table.AS400Field BETWEEN 20100216 and 20100315)


    In SQL if I wanted to run this the WHERE clause would look like this:-

    WHERE
    ((AS400Table.AS400Field BETWEEN dateadd(mm, datediff(mm, 0, getdate()) - 2, 0) AND SELECT dateadd(mm, datediff(mm, 0, getdate()) +1, -1))
    or
    ((AS400Table.AS400Field BETWEEN SELECT dateadd(mm, datediff(mm, 0, getdate()) - 1, +15) AND SELECT dateadd(mm, datediff(mm, 0, getdate()) , +14))

    Since the data is being pulled from AS400 the syntax will not work, can anyone assist me in automating this Data range so I can pull the correct Data from AS400 please?

    Thanks in advance,
    Mitch....

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    something like this:
    Code:
    between 
      current_date - day(current_date) + 1 days
    and
      current_date + 1 month - day(current_date + 1 month) days
    I guess you can figure out the other one by yourself.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2006
    Posts
    26
    Thanks Nick.
    I edited the DTS SQL query using BETWEEN current_date - day(current_date) + 1 days AND current_date + 1 month - day(current_date + 1 month) days) and the error message I get is as follows:

    SQL0182 - A Date, time , or timestamp expression not valid.

    I'm not sure if this is because AS400 keeps the date as '20100216' ?

    Any ideas?

    Thanks,
    Mitch....

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try "current date" instead of "current_date"
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    By the way, here's the manual: iSeries Information Center
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    WHERE
    (AS400Table.AS400Field BETWEEN 20100301 AND 20100331)
    or
    (AS400Table.AS400Field BETWEEN 20100216 and 20100315)
    What is a data type of AS400Table.AS400Field?

    Try
    BETWEEN current_date - (day(current_date) + 1) days AND current_date + 1 month - day(current_date + 1 month) days

    If you want this month(e.g. BETWEEN '2010-03-01' AND '2010-03-31'), try
    BETWEEN current_date - (day(current_date) - 1) days AND current_date + 1 month - day(current_date + 1 month) days

  7. #7
    Join Date
    Sep 2006
    Posts
    26
    Thanks guys.
    Just checked the table out on AS400 and just discovered that the field in that table is not in fact date type but 'Numeric Zoned' so... i'll have to rethink my approach.
    Sorry it took me so long to reply, been workign elsewheer but really appreciate your help!

    Mitch......

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by VegaLA View Post
    Thanks guys.
    Just checked the table out on AS400 and just discovered that the field in that table is not in fact date type but 'Numeric Zoned' so...
    That is ironic because DB2 iSeries is not really DB2, so...
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •