ssms to oracle openquery DATEADD

Question

New to using Openquery and trying to pull data from current month and last month. typically I would just use DATEADD for getting specific dates but OpenQuery doesn't seem to like it. Any tips on how to pull from current month and previous month?

Here is what I have tried so far.

SELECT * FROM OPENQUERY(TestServer,'

        SELECT name
            ,number
            ,create_date
            ,carton
            ,trailer
            ,bol
        FROM TEST.TESTING_VIEW1 TestV
        WHERE (TestV.create_date>=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) OR TestV.create_date IS NULL)
    ')

That query is run on the Oracle server and Oracle has no `dateadd()` you can use `current_date - interval '1' month` instead, or `add_month(current_date, -1)` — , Mar 06 '19 at 21:39

score 1 · Accepted Answer · answered Mar 06 '19 at 21:42

You could use:

SELECT * 
FROM OPENQUERY(TestServer,'
        SELECT name
            ,number
            ,create_date
            ,carton
            ,trailer
            ,bol
        FROM TEST.TESTING_VIEW1 TestV
        WHERE (TestV.create_date>= add_months(TRUNC(current_date,''MONTH''), -1) 
           OR TestV.create_date IS NULL)
    ');

SQL Server to Oracle:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
<=> 
add_months(TRUNC(current_date,'MONTH'), -1)

db<>fiddle demo

ssms to oracle openquery DATEADD

1 Answers1