Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    30

    Unanswered: Upload from db2 to SQL Server 2014 night data.

    Hello,

    I am running the latest version of iSeries (AS/400) I want to do a nightly upload into SQL Server table (2014). My problem is the invDate field is 6 digits (year/month/day):
    for the date 160620 is really 06/20/16. How do I get to read only today's date? Below is my code, but I don't know how to code to get for today's date.

    Select orderID, invDate, OrderAmt
    from OrderDetail
    Where invDate = Current(Date)


    Any help will be appreciate. Thank you.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You don't provide enough context for me to answer your question simply.

    If the code that you posted runs in a DB2 context (probably vla a linked query), then it is fine as you wrote it. An example would be:
    Code:
    INSERT INTO SQLServerTable (orderID, invDate, OrderAmount)
        SELECT orderID, invDate, OrderAmount
            FROM OpenQuery(DB2, 'SELECT orderID, invDate, OrderAmount
                FROM OrderDetail
                WHERE  invDate = Current(Date);') AS DB2Query
    If you have already copied rows to a scratch OrderDetail table on SQL Server and need to extract only the current day's data, then there are a couple of choices based on the datatype you picked for the invDate column. The universal solution would be:
    Code:
    INSERT INTO SQLTable (orderID, invDate, OrderAmt)
        SELECT orderID, invDate, OrderAmt
            FROM OrderDetail
            WHERE  DateAdd(day, DateDiff(day, GetDate(), 0), 0) <= invDate
                AND invDate < DateAdd(day, DateDiff(day, GetDate(), 0), 1);
    This is a bit convoluted, but it works on all of the DATE datatypes.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2013
    Posts
    30
    Thanks Pat
    I will give it a try.


    Quote Originally Posted by Pat Phelan View Post
    You don't provide enough context for me to answer your question simply.

    If the code that you posted runs in a DB2 context (probably vla a linked query), then it is fine as you wrote it. An example would be:
    Code:
    INSERT INTO SQLServerTable (orderID, invDate, OrderAmount)
        SELECT orderID, invDate, OrderAmount
            FROM OpenQuery(DB2, 'SELECT orderID, invDate, OrderAmount
                FROM OrderDetail
                WHERE  invDate = Current(Date);') AS DB2Query
    If you have already copied rows to a scratch OrderDetail table on SQL Server and need to extract only the current day's data, then there are a couple of choices based on the datatype you picked for the invDate column. The universal solution would be:
    Code:
    INSERT INTO SQLTable (orderID, invDate, OrderAmt)
        SELECT orderID, invDate, OrderAmt
            FROM OrderDetail
            WHERE  DateAdd(day, DateDiff(day, GetDate(), 0), 0) <= invDate
                AND invDate < DateAdd(day, DateDiff(day, GetDate(), 0), 1);
    This is a bit convoluted, but it works on all of the DATE datatypes.

    -PatP

Posting Permissions

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